package com.cloudweb.oa.controller;

import cn.js.fan.base.ObjectCache;
import cn.js.fan.cache.jcs.RMCache;
import cn.js.fan.db.Conn;
import cn.js.fan.db.ResultIterator;
import cn.js.fan.db.ResultRecord;
import cn.js.fan.util.*;
import cn.js.fan.util.file.FileUtil;
import cn.js.fan.web.Global;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cloudweb.oa.api.ILicense;
import com.cloudweb.oa.api.ILicenseUtil;
import com.cloudweb.oa.base.IConfigUtil;
import com.cloudweb.oa.entity.Department;
import com.cloudweb.oa.exception.ValidateException;
import com.cloudweb.oa.service.IDepartmentService;
import com.cloudweb.oa.utils.*;
import com.cloudweb.oa.vo.Result;
import com.cloudwebsoft.framework.base.QDBConfig;
import com.cloudwebsoft.framework.db.DataSource;
import com.cloudwebsoft.framework.db.JdbcTemplate;
import com.cloudwebsoft.framework.security.ProtectConfig;
import com.cloudwebsoft.framework.util.IPUtil;
import com.cloudwebsoft.framework.util.LogUtil;
import com.redmoon.oa.Config;
import com.redmoon.oa.SpConfig;
import com.redmoon.oa.db.SequenceManager;
import com.redmoon.oa.dept.DeptDb;
import com.redmoon.oa.fileark.plugin.PluginMgr;
import com.redmoon.oa.flow.FormDb;
import com.redmoon.oa.flow.FormForm;
import com.redmoon.oa.flow.SQLGeneratorFactory;
import com.redmoon.oa.flow.macroctl.MacroCtlMgr;
import com.redmoon.oa.kernel.License;
import com.redmoon.oa.kernel.LicenseUtil;
import com.redmoon.oa.pvg.PrivDb;
import com.redmoon.oa.pvg.Privilege;
import com.redmoon.oa.sms.SMSFactory;
import com.redmoon.oa.ui.DesktopMgr;
import com.redmoon.oa.ui.LocalUtil;
import com.redmoon.oa.ui.SkinMgr;
import com.redmoon.oa.util.TwoDimensionCode;
import io.swagger.annotations.*;
import lombok.SneakyThrows;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.commons.jcs3.access.exception.CacheException;
import org.jdom.Document;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import java.io.*;

import org.springframework.core.io.Resource;
import org.springframework.core.io.ClassPathResource;

import java.net.URL;
import java.net.URLDecoder;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.Vector;

import com.redmoon.kit.util.FileInfo;
import com.redmoon.kit.util.FileUpload;
import cn.js.fan.util.DateUtil;

import cn.js.fan.web.DBInfo;
import cn.js.fan.web.SkinUtil;
import com.cloudweb.oa.service.FormArchiveService;
import com.cloudweb.oa.utils.ConfigUtil;
import com.cloudweb.oa.utils.GtPushUtil;
import com.cloudweb.oa.utils.ResponseUtil;
import com.cloudweb.oa.utils.SpringUtil;
import com.cloudwebsoft.framework.db.Connection;
import com.redmoon.oa.flow.*;
import org.logicalcobwebs.proxool.ProxoolException;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import cn.js.fan.db.PrimaryKey;
import cn.js.fan.util.ParamUtil;
import cn.js.fan.util.StrUtil;
import com.redmoon.oa.visual.ModuleSetupDb;

import java.util.*;
import java.sql.*;
import com.redmoon.oa.util.*;
import org.apache.commons.collections.map.HashedMap;
import com.cloudweb.oa.service.DataDictService;
import com.redmoon.oa.flow.FormMgr;

import org.jdom.*;
import org.jdom.input.*;
import org.xml.sax.InputSource;
import com.cloudweb.oa.utils.ProxoolUtil;
import com.redmoon.oa.sys.SysUtil;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;


@Controller
@RequestMapping("/setup")
public class SetupController {

    @Autowired
    HttpServletRequest request;

    @Autowired
    ResponseUtil responseUtil;

    @Autowired
    IDepartmentService departmentService;

    @Autowired
    ConfigUtil configUtil;

    @Autowired
    SysProperties sysProperties;

    @Autowired
    ILicenseUtil licenseUtil;

    @RequestMapping(value = "/index", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String setup(Model model) throws ValidateException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }

        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));
        XMLConfig cfg = new XMLConfig("config.xml", false, "utf-8");
        model.addAttribute("oaVersion", cfg.get("oa.version"));

        // 清除缓存
        RMCache.refresh();
        RMCache rmcache = RMCache.getInstance();
        rmcache.clear();
        // 重新载入许可证
        License lic = License.getInstance();
        lic.init();
        ILicense iLicense = SpringUtil.getBean(ILicense.class);
        iLicense.init();
        Global.getInstance().init();
        SQLGeneratorFactory.init();
        GtPushUtil.init();
        SMSFactory.init();

        // 重新载入config_db.xml
        QDBConfig qdbcfg = new QDBConfig();
        qdbcfg.reload();
        LogUtil.getLog(getClass()).info("setup:重新载入数据库配置文件！");

        ProtectConfig pc = new ProtectConfig();
        pc.reload();

        SequenceManager.init();

        MacroCtlMgr.reload();

        DesktopMgr dm = new DesktopMgr();
        dm.reload();

        com.redmoon.weixin.Config.reload();
        com.redmoon.oa.android.CloudConfig.reload();
        // com.redmoon.oa.robot.Config.reload();

        com.redmoon.oa.security.Config.getInstance().refresh();

        SkinMgr.reload();

        com.redmoon.oa.visual.func.FuncMgr fm = new com.redmoon.oa.visual.func.FuncMgr();
        fm.reload();

        com.redmoon.dingding.Config.reload();

        com.redmoon.oa.flow.strategy.StrategyMgr.reload();

        com.redmoon.oa.flow.WorkflowConfig.reload();

        com.redmoon.oa.ui.menu.Config.getInstance().refresh();

        PluginMgr.reload();

        com.redmoon.oa.Config.reload();

        licenseUtil.init();

        model.addAttribute("lic", lic);

        boolean isJdk18 = true;
        String javaVer = System.getProperty("java.version");
        if (!javaVer.startsWith("1.8")) {
            isJdk18 = false;
        }
        model.addAttribute("isJdk18", isJdk18);

        //获取服务器路径
        ServletContext application = request.getSession().getServletContext();
        String serverInfo = application.getServerInfo();
        model.addAttribute("serverInfo", serverInfo);

        // address
        boolean addressInstalled = true;
        try {
            Class.forName("com.redmoon.oa.address.AddressDb");
        } catch (ClassNotFoundException cnfe) {
            addressInstalled = false;
        }

        // message
        boolean messageInstalled = true;
        try {
            Class.forName("com.redmoon.oa.message.MessageDb");
        } catch (ClassNotFoundException cnfe) {
            messageInstalled = false;
        }

        // kaoqin
        boolean kaoqinInstalled = true;
        try {
            Class.forName("com.redmoon.oa.kaoqin.KaoqinDb");
        } catch (ClassNotFoundException cnfe) {
            kaoqinInstalled = false;
        }

        // meeting
        boolean meetingInstalled = true;
        try {
            Class.forName("com.redmoon.oa.meeting.BoardroomDb");
        } catch (ClassNotFoundException cnfe) {
            meetingInstalled = false;
        }

        // Lucene
        boolean luceneInstalled = true;
        try {
            Class.forName("org.apache.lucene.document.Document");
        } catch (ClassNotFoundException cnfe) {
            luceneInstalled = false;
        }

        // Lucene Chinese support
        boolean luceneChineseInstalled = true;
        try {
            Class.forName("org.apache.lucene.analysis.cn.ChineseAnalyzer");
        } catch (ClassNotFoundException cnfe) {
            luceneChineseInstalled = false;
        }

        // JavaMail
        boolean javaMailInstalled = true;
        try {
            Class.forName("javax.mail.Address");  // mail.jar
            Class.forName("javax.activation.DataHandler"); // activation.jar
            // Class.forName("dog.mail.nntp.Newsgroup"); // nntp.jar
        } catch (ClassNotFoundException cnfe) {
            javaMailInstalled = false;
        }

        // JDBC std ext
        boolean jdbcExtInstalled = true;
        try {
            Class.forName("javax.sql.DataSource");
        } catch (ClassNotFoundException cnfe) {
            jdbcExtInstalled = false;
        }

        boolean filePrepare = addressInstalled && kaoqinInstalled && messageInstalled && meetingInstalled && javaMailInstalled && jdbcExtInstalled;
        model.addAttribute("filePrepare", filePrepare);
        model.addAttribute("javaMailInstalled", javaMailInstalled);
        model.addAttribute("jdbcExtInstalled", jdbcExtInstalled);

        License.getInstance().init();

        IConfigUtil configUtil = SpringUtil.getBean(IConfigUtil.class);
        String cfgPath = configUtil.getFilePath();

        // 改变JCS 目录
        String cloudHome = application.getRealPath("/");
        if (cloudHome != null) {
            cloudHome = cloudHome.replaceAll("\\\\", "/");
            if (cloudHome.lastIndexOf("/") != cloudHome.length() - 1) {
                cloudHome += "/";
            }
            LogUtil.getLog(getClass()).info("cloudHome=" + cloudHome);

            LogUtil.getLog(getClass()).info("init cache.ccf.");
            if (Global.getInstance().isUseCache() && !Global.getInstance().isUseRedis()) {
                PropertiesUtil propertiesUtil = new PropertiesUtil(cfgPath + "/cache.ccf");
                if (propertiesUtil.getSafeProperties() != null) {
                    LogUtil.getLog(getClass()).info("init DiskPath in cache.ccf.");
                    propertiesUtil.setValue("jcs.auxiliary.DC.attributes.DiskPath", cloudHome + "CacheTemp");
                    try {
                        propertiesUtil.saveFile(cfgPath + "/cache.ccf");
                    } catch (Exception e) {
                        throw new RuntimeException(e);
                    }
                }
            }
        }

        model.addAttribute("cfgPath", cfgPath);
        // 清除缓存
        RMCache.refresh();
        rmcache = RMCache.getInstance();
        rmcache.clear();
        LogUtil.getLog(getClass()).info("setup:清除缓存！");

        boolean propError = false;
        String errorMessage = null;
        try {
            if (cloudHome != null) {
                try {
                    File file = new File(cloudHome);
                    if (!file.exists()) {
                        propError = true;
                        errorMessage = "目录 <tt>" + cloudHome + "</tt> " +
                                "不存在。请编辑 <tt>jive_init.properties</tt> 文件" +
                                "指定正确的jiveHome目录。";
                    }
                } catch (Exception e) {
                }
                if (!propError) {
                    boolean readable = (new File(cloudHome)).canRead();
                    if (!readable) {
                        propError = true;
                        errorMessage = "<tt>cloudHome</tt> 存在于<tt>" + cloudHome +
                                "</tt>, 但是您的应用服务器没有对它的读权限。请设置目录的权限修正此问题。";
                    }
                    // Method jiveHomeWritable = jiveGlobals.getMethod("isJiveHomeWritable", null);
                    // boolean writable = ((Boolean)jiveHomeWritable.invoke(null, null)).booleanValue();
                    boolean writable = (new File(cloudHome)).canWrite();
                    if (!writable) {
                        propError = true;
                        errorMessage = "<tt>cloudHome</tt> 存在于<tt>" + cloudHome +
                                "</tt>, 但是您的应用服务器没有对它的写权限。请设置目录的权限修正此问题。";
                    }
                    // Jive Home appears to exist and to be setup correctly. Make sure that all of the proper sub-dirs exist
                    // or create them as necessary.
                    File homeFile = new File(cloudHome);
                    String[] subDirs = new String[]{"log", "upfile"};
                    for (int i = 0; i < subDirs.length; i++) {
                        File subDir = new File(cloudHome, subDirs[i]);
                        if (!subDir.exists()) {
                            subDir.mkdir();
                        }
                    }
                }
            } else {
                propError = true;
                errorMessage = "<tt>cloudHome</tt> 目录设置不正确。请参考安装文档正确设置 <tt>jive_init.properties</tt> 文件中的值。";
            }
        } catch (Exception e) {
            e.printStackTrace();
            propError = true;
            errorMessage = "检查<tt>cloudHome</tt>目录时发生异常。" +
                    "请确认您安装的系统程序是否完整！";
        }

        model.addAttribute("propError", propError);
        model.addAttribute("errorMessage", errorMessage);

        boolean check = false;
        if (propError || !isJdk18) {
            check = true;
        }
        model.addAttribute("check", check);

        String dbType = StrUtil.getNullStr(Global.db);
        model.addAttribute("dbType", dbType);

        String contextpath = request.getContextPath();
        model.addAttribute("contextpath", contextpath);
//        model.addAttribute("request", request);

        return "th/setup/setup";
    }

    @ApiOperation(value = "选择数据库", notes = "选择数据库", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "db", value = "数据库源", required = true, dataType = "String"),
    })
    @ApiResponses({@ApiResponse(code = 200, message = "操作成功")})
    @ResponseBody
    @RequestMapping(value = "/selectDb", method = RequestMethod.POST, produces = {"application/json;charset=UTF-8;"})
    public Result<Object> selectDb(@RequestParam(value = "db") String oadb) {
        JSONObject json = new JSONObject();
        String url = "";
        String db = "";
        if ("mysql".equals(oadb)) {
            db = "MySQL";
            url = "setup2";
//        } else if ("oracle".equals(oadb)) {
//            db = "Oracle";
//            url = "setup2_oracle";
//        } else if ("mssql".equals(oadb)) {
//            db = "SQLServer";
//            url = "setup2_mssql";
        } else if ("PostGreSql".equals(oadb)) {
            db = "PostGreSql";
            url = "postGreSql";
        } else {
            return new Result<>(false, "请选择数据库类型");
        }

        XMLConfig cfg = new XMLConfig("config_sys.xml", false, "utf-8");
        cfg.set("Application.db", db);
        cfg.writemodify();
        Global.getInstance().init();

        json.put("url", url);
        return new Result<>(json);
    }

    @RequestMapping(value = "/setup2", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String setup2Mysql(Model model) throws ValidateException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }

        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));
        Config cfg = Config.getInstance();
        model.addAttribute("oaVersion", cfg.get("oa.version"));

        License lic = License.getInstance();
        lic.init();
        model.addAttribute("lic", lic);

        String url;
        String user;
        String pwd;
        int maximumConnectionCount;

        IConfigUtil configUtil = SpringUtil.getBean(IConfigUtil.class);
        String cfgPath = configUtil.getFilePath();
        LogUtil.getLog(getClass()).info("cfgPath=" + cfgPath);
        // 先找外部配置文件，找不到，则用jar包内的
        PropertiesUtil propertiesUtil = new PropertiesUtil(cfgPath + "/application.properties");
        // 如果没有外部配置文件，则在jar包中找
        if (propertiesUtil.getSafeProperties() == null) {
            Resource resource = new ClassPathResource("application.properties");
            InputStream is = null;
            try {
                is = resource.getInputStream();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
            LogUtil.getLog(getClass()).info("载入jar包中的application.properties");
            propertiesUtil = new PropertiesUtil(is);
            url = propertiesUtil.getValue("spring.datasource.url");
            user = propertiesUtil.getValue("spring.datasource.username");
            pwd = propertiesUtil.getValue("spring.datasource.password");
            maximumConnectionCount = StrUtil.toInt(propertiesUtil.getValue("spring.datasource.maxActive"), 200);
        } else {
            // 如果有外部配置文件
            url = propertiesUtil.getValue("spring.datasource.url");
            // 如果从外部配置文件中取到
            if (!StrUtil.isEmpty(url)) {
                LogUtil.getLog(getClass()).info("载入" + cfgPath + "中的application.properties");
                user = propertiesUtil.getValue("spring.datasource.username");
                pwd = propertiesUtil.getValue("spring.datasource.password");
                maximumConnectionCount = StrUtil.toInt(propertiesUtil.getValue("spring.datasource.maxActive"), 200);
            } else {
                // 如果从外部配置文件中未取到，则从class path中取
                Resource resource = new ClassPathResource("application.properties");
                InputStream is = null;
                try {
                    is = resource.getInputStream();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
                LogUtil.getLog(getClass()).info("载入class path中的application.properties");
                propertiesUtil = new PropertiesUtil(is);
                url = propertiesUtil.getValue("spring.datasource.url");
                user = propertiesUtil.getValue("spring.datasource.username");
                pwd = propertiesUtil.getValue("spring.datasource.password");
                maximumConnectionCount = StrUtil.toInt(propertiesUtil.getValue("spring.datasource.maxActive"), 200);
            }
        }
        model.addAttribute("cfgPath", cfgPath);
        model.addAttribute("user", user);
        model.addAttribute("pwd", pwd);

        int beginIndex = url.indexOf("//");
        String ip = "";
        if (beginIndex != -1) {
            ip = url.substring(beginIndex + 2, url.indexOf(":", beginIndex));
        }

        String database = "";
        int secondIndex = -1;
        int port = 3306;
        if (Global.db.equalsIgnoreCase(Global.DB_MYSQL)) {
            secondIndex = url.indexOf("/", beginIndex + 2);
            if (secondIndex != -1) {
                int p = url.lastIndexOf("?");
                if (p != -1) {
                    database = url.substring(secondIndex + 1, p);
                }
            }
            if (url.lastIndexOf(":") != -1) {
                int p = url.lastIndexOf(":");
                if (p != -1) {
                    port = StrUtil.toInt(url.substring(p + 1, secondIndex), 3306);
                }
            }
        } else {
            secondIndex = url.indexOf("/", beginIndex + 2);
            if (secondIndex != -1) {
                database = url.substring(secondIndex + 1);
            }
            if (url.lastIndexOf(":") != -1) {
                int p = url.lastIndexOf(":");
                if (p != -1) {
                    port = StrUtil.toInt(url.substring(p + 1, secondIndex), 5432);
                }
            }
        }

        model.addAttribute("ip", ip);
        model.addAttribute("port", port);
        model.addAttribute("database", database);
        model.addAttribute("secondIndex", secondIndex);
        model.addAttribute("maximumConnectionCount", maximumConnectionCount);

        return "th/setup/setup2";
    }

    @RequestMapping(value = "/postGreSql", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String setup2PostGreSql(Model model) throws ValidateException, IOException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }
        Config cfg = Config.getInstance();
        model.addAttribute("oaVersion", cfg.get("oa.version"));

        License lic = License.getInstance();
        lic.init();
        model.addAttribute("lic", lic);
        String url;
        String user;
        String pwd;
        int maximumConnectionCount;

        IConfigUtil configUtil = SpringUtil.getBean(IConfigUtil.class);
        String cfgPath = configUtil.getFilePath();
        LogUtil.getLog(getClass()).info("cfgPath=" + cfgPath);
        // 先找外部配置文件，找不到，则用jar包内的
        PropertiesUtil propertiesUtil = new PropertiesUtil(cfgPath + "/application.properties");
        // 如果没有外部配置文件，则在jar包中找
        if (propertiesUtil.getSafeProperties() == null) {
            Resource resource = new ClassPathResource("application.properties");
            InputStream is = resource.getInputStream();
            LogUtil.getLog(getClass()).info("载入jar包中的application.properties");
            propertiesUtil = new PropertiesUtil(is);
            url = propertiesUtil.getValue("spring.datasource.url");
            user = propertiesUtil.getValue("spring.datasource.username");
            pwd = propertiesUtil.getValue("spring.datasource.password");
            maximumConnectionCount = StrUtil.toInt(propertiesUtil.getValue("spring.datasource.maxActive"), 200);
        } else {
            // 如果有外部配置文件
            url = propertiesUtil.getValue("spring.datasource.url");
            // 如果从外部配置文件中取到
            if (!StrUtil.isEmpty(url)) {
                LogUtil.getLog(getClass()).info("载入" + cfgPath + "中的application.properties");
                user = propertiesUtil.getValue("spring.datasource.username");
                pwd = propertiesUtil.getValue("spring.datasource.password");
                maximumConnectionCount = StrUtil.toInt(propertiesUtil.getValue("spring.datasource.maxActive"), 200);
            } else {
                // 如果从外部配置文件中未取到，则从class path中取
                Resource resource = new ClassPathResource("application.properties");
                InputStream is = resource.getInputStream();
                LogUtil.getLog(getClass()).info("载入class path中的application.properties");
                propertiesUtil = new PropertiesUtil(is);
                url = propertiesUtil.getValue("spring.datasource.url");
                user = propertiesUtil.getValue("spring.datasource.username");
                pwd = propertiesUtil.getValue("spring.datasource.password");
                maximumConnectionCount = StrUtil.toInt(propertiesUtil.getValue("spring.datasource.maxActive"), 200);
            }
        }

        int beginIndex = url.indexOf("//");
        String ip = "";
        if (beginIndex != -1) {
            ip = url.substring(beginIndex + 2, url.indexOf(":", beginIndex));
        }
        int secondIndex = url.indexOf("/", beginIndex + 2);
        String database = "";
        if (secondIndex != -1) {
            int p = url.lastIndexOf("?");
            if (p != -1) {
                database = url.substring(secondIndex + 1, p);
            }
//            database = url.substring(secondIndex + 1);
        }
        int port = 5432;
        if (url.lastIndexOf(":") != -1) {
            int p = url.lastIndexOf(":");
            if (p != -1) {
                port = StrUtil.toInt(url.substring(p + 1, secondIndex), 5432);
            }
        }
        String path = cfgPath + File.separator + "application.properties";
        model.addAttribute("path", path);
        model.addAttribute("user", user);
        model.addAttribute("pwd", pwd);
        model.addAttribute("ip", ip);
        model.addAttribute("port", port);
        model.addAttribute("database", database);
        model.addAttribute("maximumConnectionCount", maximumConnectionCount);
        return "th/setup/setup2_postgresql";
    }

    @RequestMapping(value = "/setup3", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String setup3(Model model) throws ValidateException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }

        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));

        License lic = License.getInstance();
        lic.init();
        model.addAttribute("lic", lic);

        XMLConfig cfg = new XMLConfig("config_sys.xml", false, "utf-8");
        XMLConfig cfg_oa = new XMLConfig("config.xml", false, "utf-8");
        model.addAttribute("oaVersion", cfg_oa.get("oa.version"));
        int isHideStep = ParamUtil.getInt(request, "isHideStep", 0);
        model.addAttribute("isHideStep", isHideStep);
        DeptDb dd = new DeptDb();
        dd = dd.getDeptDb(DeptDb.ROOTCODE);
        model.addAttribute("deptName", dd.getName());
        model.addAttribute("appName", Global.AppName);
        model.addAttribute("serverName", request.getServerName());
        model.addAttribute("serverPort", request.getServerPort());
        model.addAttribute("requestSupportCN", Global.requestSupportCN);
        model.addAttribute("internetFlag", Global.internetFlag);
        model.addAttribute("isFormalOpen", Global.getInstance().isFormalOpen());
        model.addAttribute("timeZone", Global.timeZone.getID());

        boolean isCluster = Global.isCluster();
        String realPathRecommand;
        String realPath = "";
        IConfigUtil configUtil = SpringUtil.getBean(IConfigUtil.class);
        if (configUtil.isRunJar()) {
            realPathRecommand = Global.getRealPath();
            realPath = Global.getRealPath();
        } else {
            // 当以内置tomcat运行时，取得的为：WEB-INF/classes/META-INF/resources/
            // realPathRecommand = application.getRealPath("/").replaceAll("\\\\", "/");
            realPathRecommand = Global.getAppPath();
            LogUtil.getLog(getClass()).info("realPathRecommand=" + realPathRecommand);
            if (isCluster) {
                realPath = Global.getRealPath();
            } else {
                if ("".equals(Global.getRealPath())) {
                    realPath = realPathRecommand;
                } else {
                    realPath = Global.getRealPath();
                }
            }
        }
        model.addAttribute("isCluster", isCluster);
        model.addAttribute("realPath", realPath);
        model.addAttribute("realPathRecommand", realPathRecommand);

        // 注意vPath用于生成登录界面的二维码
        String vPath = Global.virtualPath;
        // if ("".equals(vPath)) {
        vPath = request.getContextPath();
        if (vPath.startsWith("/")) {
            vPath = vPath.substring(1);
        }
        // }
        model.addAttribute("vPath", vPath);

        String urlIp = request.getServerName();
        String server = cfg.get("Application.server");
        model.addAttribute("server", server);
        model.addAttribute("isDomain", IpUtil.isDomain(urlIp));
        model.addAttribute("isInnerIp", IpUtil.isInnerIP(urlIp));
        model.addAttribute("isInnerIpServer", IpUtil.isInnerIP(server));

        String url = "setup2";
        if (isHideStep == 0) {
            String oadb = ParamUtil.get(request, "oadb");
            if (Global.db.equals(Global.DB_MYSQL)) {
                url = "setup2";
            }/* else if ("oracle".equals(oadb)) {
                url = "setup2_oracle.jsp";
            } else if ("mssql".equals(oadb)) {
                url = "setup2_mssql.jsp";
            } */ else if ("PostGreSql".equals(oadb)) {
                url = "setupPostGreSql";
            }
        }
        model.addAttribute("url", url);
        model.addAttribute("isClusterNoDisplay", Global.getInstance().isClusterNoDisplay());
        model.addAttribute("getInstanceDebug", Global.getInstance().isDebug());

        return "th/setup/setup3";
    }

    @RequestMapping(value = "/license", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String license(Model model) {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }

        String licFileName = (String) request.getAttribute("licFileName");
        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));

        License.getInstance().init();

        Config oaCfg = new Config();
        SpConfig spCfg = new SpConfig();
        String version = StrUtil.getNullStr(oaCfg.get("version"));
        String spVersion = StrUtil.getNullStr(spCfg.get("version"));
        model.addAttribute("version", version);
        model.addAttribute("spVersion", spVersion);

        if (StrUtil.isEmpty(licFileName)) {
            model.addAttribute("isShowCurLicense", true);

            License license = License.getInstance();

            model.addAttribute("license", license);
            //企业号
            model.addAttribute("enterpriseNum", license.getEnterpriseNum() != null ? license.getEnterpriseNum() : license.getName());
            //类型
            model.addAttribute("LicenseType", license.getType().equals(License.TYPE_COMMERICAL) ? "免费版" : license.getType());
            //到期时间
            model.addAttribute("licenseExpiresDate", DateUtil.format(license.getExpiresDate(), "yyyy-MM-dd"));//到期时间
            //试用版
            model.addAttribute("licenseTrial", license.isTrial() ? "是" : "否");
            //绑定端口
            model.addAttribute("portUnlimit", license.isPortUnlimit() ? "否":"是");
            model.addAttribute("versionType", license.getVersionType().equals(License.VERSION_TYPE_PREMIUM) ? "卓越版" : "优享版");
        } else {
            model.addAttribute("isShowCurLicense", false);

            LicenseUtil lu = LicenseUtil.getInstance();
            lu.setLicenseFilePath(licFileName);
            lu.verifyByFile();

            model.addAttribute("lu", lu);

            //试用版
            model.addAttribute("isTrial", lu.isTrial() ? "是" : "否");
            //流程设计器
            model.addAttribute("flowDesigner", lu.getFlowDesigner().equals(License.FLOW_DESIGNER_A) ? "A版（仅可在IE下设计）" : "X版");
            //类型
            model.addAttribute("type", lu.getType().equals(License.TYPE_COMMERICAL) ? "免费版" : lu.getType());
            //到期时间
            model.addAttribute("expiresDate", DateUtil.format(lu.getExpiresDate(), "yyyy-MM-dd"));
            //绑定端口
            model.addAttribute("portUnlimit", lu.isPortUnlimit() ? "否":"是");
            model.addAttribute("versionType", lu.getVersionType().equals(License.VERSION_TYPE_PREMIUM) ? "卓越版" : "优享版");
        }
        return "th/setup/license";
    }

    @ApiOperation(value = "上传许可证", notes = "上传许可证", httpMethod = "POST")
    @RequestMapping(value = "/uploadLicense", method = RequestMethod.POST, produces = {"application/json;charset=UTF-8;"})
    public String uploadLicense(Model model) {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }

        //获取服务器路径
        ServletContext application = request.getSession().getServletContext();
        FileUpload fileUpload = new FileUpload();
        fileUpload.setMaxFileSize(Global.FileSize); // 每个文件最大30000K 即近300M
        String[] extnames = {"dat"};
        fileUpload.setValidExtname(extnames); //设置可上传的文件类型
        int ret = 0;
        try {
            ret = fileUpload.doUpload(application, request);
        } catch (IOException e) {
            LogUtil.getLog(getClass()).error(e);
        }

        if (ret == FileUpload.RET_SUCCESS) {
            Vector<FileInfo> v = fileUpload.getFiles();
            FileInfo fi = null;
            if (v.size() > 0) {
                fi = v.get(0);
            }
            if (fi != null) {
                // 置保存路径
                String filepath = Global.getRealPath() + FileUpload.TEMP_PATH + "/";
                fileUpload.setSavePath(filepath);
                fileUpload.writeFile(true);
                // 使用随机名称写入磁盘
                // fi.writeToPath(filepath);
                String licFileName = filepath + fi.getDiskName();
                request.setAttribute("licFileName", licFileName);
            }
        } else {
            model.addAttribute("msg", "文件格式错误");
            return "th/error/error";
        }
        return "forward:license";
    }

    @ApiOperation(value = "更换许可证", notes = "更换许可证", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "licFileName", value = "许可证文件", required = true, dataType = "String"),
    })
    @ApiResponses({@ApiResponse(code = 200, message = "操作成功")})
    @ResponseBody
    @RequestMapping(value = "/changeLicense", method = RequestMethod.POST, produces = {"application/json;charset=UTF-8;"})
    public Result<Object> changeLicense(@RequestParam String licFileName) {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                return new Result<>(false, "请以管理员身份登录以后再操作");
            }
        }

        ConfigUtil configUtil = SpringUtil.getBean(ConfigUtil.class);
        File file = new File(licFileName);
        if (file.exists()) {
            FileUtil.CopyFile(licFileName, configUtil.getFilePath() + File.separator + "license.dat");
            License.getInstance().init();
            ILicense iLicense = SpringUtil.getBean(ILicense.class);
            iLicense.init();
        } else {
            return new Result<>(false, "许可证文件: " + licFileName + " 不存在");
        }
        return new Result<>(true);
    }

    @RequestMapping(value = "/sysActivate", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String sysActivate(Model model) {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }

        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));

        String activateRequestCode = licenseUtil.genActivateRequestCode();
        model.addAttribute("activateRequestCode", activateRequestCode);

        License lic = License.getInstance();
        lic.init();

        model.addAttribute("lic", lic);
        com.redmoon.oa.kernel.License license = com.redmoon.oa.kernel.License.getInstance();

        model.addAttribute("userCount", license.getUserCount());
        model.addAttribute("type", license.getType());
        model.addAttribute("domain", license.getDomain());
        model.addAttribute("dateFormat", DateUtil.format(license.getExpiresDate(), "yyyy-MM-dd"));
        model.addAttribute("license", license);
        model.addAttribute("getSkinPath", SkinMgr.getSkinPath(request));
        Config cfg = Config.getInstance();
        boolean isCloud = cfg.getBooleanProperty("isCloud");
        String url;
        if (isCloud) {
            url = "https://www.xiaocaicloud.com/public/license/onlineActivateV9.do";
        } else {
            url = "http://localhost:8899/oa_ide/public/license/onlineActivateV9.do";
        }
        model.addAttribute("url", url);
        return "th/setup/sys_activate";
    }

    @RequestMapping(value = "/sysActivateOffline", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String sysActivateOffline(Model model) {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }

        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));
        model.addAttribute("serial", licenseUtil.getSerial());
        return "th/setup/sys_activate_offline";
    }

    @RequestMapping(value = "/upgradeFlowByDb", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String upgradeFlowByDb(Model model) throws SQLException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }
        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));
        cn.js.fan.web.Config cfg = new cn.js.fan.web.Config();
        List<String> diInfo = new ArrayList<>();
        for (DBInfo di : cfg.getDBInfos()) {
            diInfo.add(di.name);
            diInfo.add(di.isDefault ? "selected" : "");
        }
        model.addAttribute("diInfo", cfg.getDBInfos());

        String dbSource = ParamUtil.get(request, "dbSource");
        model.addAttribute("dbSource", dbSource);
        model.addAttribute("ifDbSource", "".equals(dbSource));

        if (!"".equals(dbSource)) {
            Leaf lf = new Leaf();
            lf = lf.getLeaf(Leaf.CODE_ROOT);
            String sqlNew = "select code,name from flow_directory where code<>'root' order by code asc";
            JdbcTemplate jt = new JdbcTemplate(new Connection(dbSource));
            ResultIterator ri = jt.executeQuery(sqlNew);
            Vector<Leaf> v = new Vector<>();
            lf.getAllChild(v, lf);
            JSONArray irInfo = new JSONArray();
            while (ri.hasNext()) {
                ResultRecord rr = ri.next();
                String codeNew = "", nameNew = "", name = "";
                boolean isFound = false;
                codeNew = rr.getString(1);
                nameNew = rr.getString(2);
                for (Leaf leaf : v) {
                    lf = leaf;
                    if (lf.getCode().equals(codeNew)) {
                        name = lf.getName();
                        isFound = true;
                        break;
                    }
                }
                JSONObject json = new JSONObject();
                json.put("isFound", isFound);
                json.put("codeNew", codeNew);
                json.put("nameNew", nameNew);
                json.put("name", name);
                irInfo.add(json);
            }
            model.addAttribute("irInfo", irInfo);
        }
        return "th/setup/upgrade_flow_by_db";
    }

    @RequestMapping(value = "/upgradeFlowByDbBtn", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    @ResponseBody
    public String upgradeFlowByDbBtn() throws SQLException {
        /**
         * 流程同步工具，同步表单中的字段
         * 以本应用为旧版数据库，其它数据源为新版数据库
         */
        String op = ParamUtil.get(request, "op");
        if ("sync".equals(op)) {
            JSONObject json = new JSONObject();
            String dbSource = ParamUtil.get(request, "dbSource");
            String typeCode = ParamUtil.get(request, "typeCode");
            String sqlNew = "select * from flow_directory where code=?";
            JdbcTemplate jt = new JdbcTemplate(new Connection(dbSource));
            ResultIterator ri = jt.executeQuery(sqlNew, new Object[]{typeCode});
            if (ri.hasNext()) {
                ResultRecord rr = ri.next();
                Leaf lf = new Leaf();
                lf = lf.getLeaf(typeCode);
                if (lf == null) {
                    json.put("ret", 0);
                    json.put("msg", "本系统中流程不存在，请在新系统的表单管理中导出，然后导入本系统，导入时会带有流程及基础数据");
                    return json.toString();
                }
                String sql = "update flow_directory set name=" + StrUtil.sqlstr(rr.getString("name")) +
                        ",description=" + StrUtil.sqlstr(rr.getString("description")) +
                        ",parent_code=" + StrUtil.sqlstr(rr.getString("parent_code")) +
                        ",type=" + rr.getInt("type") + ",isHome=" + (rr.getInt("isHome") == 1 ? "1" : "0") + ",doc_id=" + rr.getInt("doc_id") + ",template_id=" + rr.getInt("template_id") +
                        ",orders=" + rr.getInt("orders") + ",layer=" + rr.getInt("layer") + ",child_count=" + rr.getInt("child_count") + ",pluginCode=" + StrUtil.sqlstr(rr.getString("pluginCode")) +
                        ",formCode=" + StrUtil.sqlstr(rr.getString("formCode")) + ",dept=" + StrUtil.sqlstr(rr.getString("dept")) + ",is_open=" + (rr.getInt("is_open") == 1 ? 1 : 0) + ",unit_code=" + StrUtil.sqlstr(rr.getString("unit_code")) + ",is_debug=" + (rr.getInt("is_debug") == 1 ? 1 : 0) +
                        ",is_mobile_start=" + (rr.getInt("is_mobile_start") == 1 ? 1 : 0) +
                        ",is_mobile_location=" + (rr.getInt("is_mobile_location") == 1 ? 1 : 0) +
                        ",is_mobile_camera=" + (rr.getInt("is_mobile_camera") == 1 ? 1 : 0) +
                        ",query_id=" + rr.getInt("query_id") +
                        ",query_role=" + StrUtil.sqlstr(rr.getString("query_role")) +
                        ",query_cond_map=" + StrUtil.sqlstr(rr.getString("query_cond_map")) +
                        ",col_props=" + StrUtil.sqlstr(rr.getString("col_props")) +
                        ",cond_props=" + StrUtil.sqlstr(rr.getString("cond_props")) +
                        ",params=" + StrUtil.sqlstr(rr.getString("params")) +
                        " where code=" + StrUtil.sqlstr(typeCode);

                JdbcTemplate jdbcTemplate = new JdbcTemplate();
                boolean re = jdbcTemplate.executeUpdate(sql) == 1;
                LeafChildrenCacheMgr.removeAll();
                lf.removeAllFromCache();
                WorkflowPredefineDb workflowPredefineDb = new WorkflowPredefineDb();
                workflowPredefineDb = workflowPredefineDb.getDefaultPredefineFlow(typeCode);
                // 更新flow_predefined
                sqlNew = "select * from flow_predefined where typeCode=?";
                ri = jt.executeQuery(sqlNew, new Object[]{typeCode});
                if (ri.hasNext()) {
                    rr = ri.next();
                    sql = "update flow_predefined set flowString=?,typeCode=?,title=?,return_back=?,IS_DEFAULT_FLOW=?," +
                            "dir_code=?,examine=?,is_reactive=?,is_recall=?,return_mode=?,return_style=?,role_rank_mode=?," +
                            "props=?,views=?,scripts=?,is_light=?,link_prop=?,write_prop=?,is_distribute=?,write_db_prop=?," +
                            "msg_prop=?,is_plus=?,is_transfer=?,is_reply=?,download_count=?,can_del_on_return=? where id=?";
                    PrimaryKey primaryKey = new PrimaryKey("id", PrimaryKey.TYPE_INT);
                    String flowString = rr.getString("flowString");
                    if (flowString.equals("")) {
                        flowString = " "; // 适应SQLSERVER
                    }
                    PreparedStatement ps = null;
                    Conn conn = new Conn(Global.getDefaultDB());
                    try {
                        ps = conn.prepareStatement(sql);
                        if (flowString.equals("")) {
                            flowString = " "; // 适应SQLSERVER
                        }
                        ps.setString(1, flowString); //适应Oracle，LONG类型需放在第一个
                        ps.setString(2, typeCode);
                        ps.setString(3, rr.getString("title"));
                        ps.setInt(4, rr.getInt("return_back"));
                        ps.setInt(5, rr.getInt("IS_DEFAULT_FLOW"));
                        ps.setString(6, rr.getString("dir_code"));
                        ps.setInt(7, rr.getInt("examine"));
                        ps.setInt(8, rr.getInt("is_reactive"));
                        ps.setInt(9, rr.getInt("is_recall"));
                        ps.setInt(10, rr.getInt("return_mode"));
                        ps.setInt(11, rr.getInt("return_style"));
                        ps.setInt(12, rr.getInt("role_rank_mode"));
                        ps.setString(13, rr.getString("props"));
                        ps.setString(14, rr.getString("views"));
                        ps.setString(15, rr.getString("scripts"));
                        ps.setInt(16, rr.getInt("is_light"));
                        ps.setString(17, rr.getString("link_prop"));
                        ps.setString(18, rr.getString("write_prop"));
                        ps.setInt(19, rr.getInt("is_distribute"));
                        ps.setString(20, rr.getString("write_db_prop"));
                        ps.setString(21, rr.getString("msg_prop"));
                        ps.setInt(22, rr.getInt("is_plus"));
                        ps.setInt(23, rr.getInt("is_transfer"));
                        ps.setInt(24, rr.getInt("is_reply"));
                        ps.setInt(25, rr.getInt("download_count"));
                        ps.setInt(26, rr.getInt("can_del_on_return"));
                        ps.setInt(27, workflowPredefineDb.getId());
                        re = conn.executePreUpdate() == 1 ? true : false;
                        if (re) {
                            WorkflowPredefineCache rc = new WorkflowPredefineCache(workflowPredefineDb);
                            primaryKey.setValue(new Integer(workflowPredefineDb.getId()));
                            rc.refreshSave(primaryKey);
                        }
                    } catch (SQLException e) {
                        LogUtil.getLog(getClass()).error(e);
                    } finally {
                        if (conn != null) {
                            conn.close();
                            conn = null;
                        }
                    }
                }
            }
            json.put("ret", 1);
            json.put("msg", "操作成功");
            return json.toString();
        } else if ("syncScripts".equals(op)) {
            JSONObject json = new JSONObject();
            String dbSource = ParamUtil.get(request, "dbSource");
            String typeCode = ParamUtil.get(request, "typeCode");
            String sqlNew = "select * from flow_directory where code=?";
            JdbcTemplate jt = new JdbcTemplate(new Connection(dbSource));
            ResultIterator ri = jt.executeQuery(sqlNew, new Object[]{typeCode});
            if (ri.hasNext()) {
                ResultRecord rr = ri.next();
                Leaf lf = new Leaf();
                lf = lf.getLeaf(typeCode);
                boolean re = true;
                LeafChildrenCacheMgr.removeAll();
                lf.removeAllFromCache();
                WorkflowPredefineDb workflowPredefineDb = new WorkflowPredefineDb();
                workflowPredefineDb = workflowPredefineDb.getDefaultPredefineFlow(typeCode);
                JdbcTemplate jdbcTemplate = new JdbcTemplate();
                // 更新flow_predefined
                sqlNew = "select * from flow_predefined where typeCode=?";
                ri = jt.executeQuery(sqlNew, new Object[]{typeCode});
                if (ri.hasNext()) {
                    rr = ri.next();
                    String sql = "update flow_predefined set scripts=? where id=?";
                    PrimaryKey primaryKey = new PrimaryKey("id", PrimaryKey.TYPE_INT);
                    PreparedStatement ps = null;
                    Conn conn = new Conn(Global.getDefaultDB());
                    try {
                        ps = conn.prepareStatement(sql);
                        ps.setString(1, rr.getString("scripts"));
                        ps.setInt(2, workflowPredefineDb.getId());
                        re = conn.executePreUpdate() == 1;
                        if (re) {
                            WorkflowPredefineCache rc = new WorkflowPredefineCache(workflowPredefineDb);
                            primaryKey.setValue(new Integer(workflowPredefineDb.getId()));
                            rc.refreshSave(primaryKey);
                        }
                    } catch (SQLException e) {
                        LogUtil.getLog(getClass()).error(e);
                    } finally {
                        conn.close();
                    }
                }
            }
            json.put("ret", 1);
            json.put("msg", "操作成功");
            return json.toString();
        } else if ("syncScriptsBatch".equals(op)) {
            JSONObject json = new JSONObject();
            String typeCodes = ParamUtil.get(request, "typeCodes");
            String[] arr = StrUtil.split(typeCodes, ",");
            if (arr == null) {
                json.put("ret", 0);
                json.put("msg", "请选择流程");
                return json.toString();
            }
            String dbSource = ParamUtil.get(request, "dbSource");
            for (String typeCode : arr) {
                String sqlNew = "select * from flow_directory where code=?";
                JdbcTemplate jt = new JdbcTemplate(new Connection(dbSource));
                ResultIterator ri = jt.executeQuery(sqlNew, new Object[]{typeCode});
                if (ri.hasNext()) {
                    ResultRecord rr = ri.next();
                    Leaf lf = new Leaf();
                    lf = lf.getLeaf(typeCode);
                    boolean re = true;
                    LeafChildrenCacheMgr.removeAll();
                    lf.removeAllFromCache();
                    WorkflowPredefineDb workflowPredefineDb = new WorkflowPredefineDb();
                    workflowPredefineDb = workflowPredefineDb.getDefaultPredefineFlow(typeCode);
                    JdbcTemplate jdbcTemplate = new JdbcTemplate();
                    // 更新flow_predefined
                    sqlNew = "select * from flow_predefined where typeCode=?";
                    ri = jt.executeQuery(sqlNew, new Object[]{typeCode});
                    if (ri.hasNext()) {
                        rr = ri.next();
                        String sql = "update flow_predefined set scripts=? where id=?";
                        PrimaryKey primaryKey = new PrimaryKey("id", PrimaryKey.TYPE_INT);
                        PreparedStatement ps = null;
                        Conn conn = new Conn(Global.getDefaultDB());
                        try {
                            ps = conn.prepareStatement(sql);
                            ps.setString(1, rr.getString("scripts"));
                            ps.setInt(2, workflowPredefineDb.getId());
                            re = conn.executePreUpdate() == 1;
                            if (re) {
                                WorkflowPredefineCache rc = new WorkflowPredefineCache(workflowPredefineDb);
                                primaryKey.setValue(workflowPredefineDb.getId());
                                rc.refreshSave(primaryKey);
                            }
                        } catch (SQLException e) {
                            LogUtil.getLog(getClass()).error(e);
                        } finally {
                            conn.close();
                        }
                    }
                }
            }
            json.put("ret", 1);
            json.put("msg", "操作成功");
            return json.toString();
        }
        return "th/setup/upgrade_flow_by_db";
    }

    @RequestMapping(value = "/upgradeFormByDb", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String upgradeFormByDb(Model model) throws ValidateException, SQLException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }
        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));

        cn.js.fan.web.Config cfg = new cn.js.fan.web.Config();
        Iterator ir = cfg.getDBInfos().iterator();
        List<String> diInfo = new ArrayList<>();
        while (ir.hasNext()) {
            DBInfo di = (DBInfo) ir.next();
            diInfo.add(di.name);
            diInfo.add(di.isDefault ? "selected" : "");
        }
        model.addAttribute("diInfo", cfg.getDBInfos());

        String dbSource = ParamUtil.get(request, "dbSource");
        model.addAttribute("dbSource", dbSource);
        model.addAttribute("ifDbSource", "".equals(dbSource));

        if (!"".equals(dbSource)) {
            FormDb fd = new FormDb();
            String sqlNew = "select code,name from form order by code asc";
            JdbcTemplate jt = new JdbcTemplate(new Connection(dbSource));
            ResultIterator ri = jt.executeQuery(sqlNew);
            String sql = "select code from form order by code asc";
            Vector v = fd.list(sql);
            JSONArray codeInfo = new JSONArray();
            while (ri.hasNext()) {
                ResultRecord rr = (ResultRecord) ri.next();
                String codeNew = "", nameNew = "", name = "";
                boolean isFound = false;
                codeNew = rr.getString(1);
                nameNew = rr.getString(2);
                ir = v.iterator();
                while (ir.hasNext()) {
                    fd = (FormDb) ir.next();
                    if (fd.getCode().equals(codeNew)) {
                        name = fd.getName();
                        isFound = true;
                        break;
                    }
                }
                JSONObject json = new JSONObject();
                json.put("code",fd.getCode());
                json.put("codeNew",codeNew);
                json.put("nameNew",nameNew);
                json.put("isFound",isFound);
                json.put("name",name);
//                model.addAttribute("isFound",isFound);
                codeInfo.add(json);
                model.addAttribute("codeInfo", codeInfo);
            }
        }
        return "th/setup/upgrade_form_by_db";
    }

    @RequestMapping(value = "/upgradeFormByDbBtn", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    @ResponseBody
    public String upgradeFormByDbBtn() throws SQLException {
        /**
         * 表单同步工具，同步表单中的字段
         * 以本应用为旧版数据库，其它数据源为新版数据库
         */
        String op = ParamUtil.get(request, "op");
        if ("syncForm".equals(op)) {
            JSONObject json = new JSONObject();
            FormMgr formMgr = new FormMgr();
            String dbSource = ParamUtil.get(request, "dbSource");
            String formCode = ParamUtil.get(request, "formCode");
            String sqlNew = "select * from form where code=?";
            JdbcTemplate jt = new JdbcTemplate(new Connection(dbSource));
            ResultIterator ri = jt.executeQuery(sqlNew, new Object[]{formCode});
            if (ri.hasNext()) {
                ResultRecord rr = ri.next();
                FormDb ftd = new FormDb();
                ftd = ftd.getFormDb(formCode);
                ftd.setName(rr.getString("name"));
                ftd.setOldContent(rr.getString("content"));
                ftd.setContent(rr.getString("content"));
                ftd.setFlowTypeCode(rr.getString("flowTypeCode"));
                ftd.setHasAttachment(rr.getInt("has_attachment") == 1);
                ftd.setIeVersion(rr.getString("ie_version"));
                ftd.setLog(rr.getInt("is_log") == 1);
                ftd.setProgress(rr.getInt("is_progress") == 1);
                ftd.setUnitCode(rr.getString("unit_code"));
                ftd.setOnlyCamera(rr.getInt("is_only_camera") == 1);
                ftd.setFlow(rr.getInt("isFlow") == 1);
                ftd.setCheckSetup(rr.getString("check_setup"));
                ftd.setViewSetup(rr.getString("view_setup"));
                boolean re = ftd.save();
                if (re) {
                    // 当表单更新时，处理表单归档记录
                    FormArchiveService formArchiveService = SpringUtil.getBean(FormArchiveService.class);
                    formArchiveService.onFormUpdate(ftd);
                    formMgr.regenerateFormView(formCode);
                }
            }
            json.put("ret", 1);
            json.put("msg", "操作成功");
            return json.toString();
        }
        return "th/setup/upgrade_form_by_db";
    }

    @RequestMapping(value = "/upgradeModuleByDb", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String upgradeModuleByDb(Model model) throws ValidateException, SQLException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }
        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));

        String dbSource = ParamUtil.get(request, "dbSource");
        String formCode = ParamUtil.get(request, "formCode");
        model.addAttribute("formCode", formCode);
        if ("".equals(dbSource) || "".equals(formCode)) {
            return SkinUtil.makeErrMsg(request, SkinUtil.LoadString(request, "err_id"));
        }
        Boolean isDbFrom = "".equals(dbSource) || "".equals(formCode);
        model.addAttribute("ifDbSource", isDbFrom);

        if (!isDbFrom) {
            String sqlNew = "select * from visual_module_setup where form_code=? order by code asc";
            JdbcTemplate jt = new JdbcTemplate(new Connection(dbSource));
            ResultIterator ri = jt.executeQuery(sqlNew, new Object[]{formCode});
            ModuleSetupDb msd = new ModuleSetupDb();
            String sql = msd.getTable().getSql("listForForm") + StrUtil.sqlstr(formCode);
            sql += " order by kind asc, name asc";
            Vector v = msd.list(sql);
            JSONArray codeInfo = new JSONArray();
            while (ri.hasNext()) {
                ResultRecord rr = (ResultRecord) ri.next();
                String codeNew = "", nameNew = "", name = "";
                boolean isFound = false;
                codeNew = rr.getString("code");
                nameNew = rr.getString("name");
                Iterator ir = v.iterator();
                while (ir.hasNext()) {
                    msd = (ModuleSetupDb) ir.next();
                    if (msd.getCode().equals(codeNew)) {
                        name = msd.getName();
                        isFound = true;
                        break;
                    }
                }
                JSONObject json = new JSONObject();
                json.put("codeNew", codeNew);
                json.put("nameNew", nameNew);
                json.put("code", msd.getCode());
                json.put("isFound", isFound);
                json.put("name", name);
                codeInfo.add(json);
            }
            model.addAttribute("codeInfo", codeInfo);
        }
        return "th/setup/upgrade_module_by_db";
    }

    @RequestMapping(value = "/upgradeModuleByDbBtn", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    @ResponseBody
    public String upgradeModuleByDbBtn() throws SQLException {
        /**
         * 模块同步工具，同步表单中的字段
         * 以本应用为旧版数据库，其它数据源为新版数据库
         */
        String op = ParamUtil.get(request, "op");
        if ("syncModule".equals(op)) {
            JSONObject json = new JSONObject();
            FormMgr formMgr = new FormMgr();
            String dbSource = ParamUtil.get(request, "dbSource");
            String formCode = ParamUtil.get(request, "formCode");
            String moduleCode = ParamUtil.get(request, "moduleCode");
            String action = ParamUtil.get(request, "action");
            ModuleSetupDb msd = new ModuleSetupDb();
            boolean re = false;
            String sqlNew = "select * from visual_module_setup where code=?";
            JdbcTemplate jt = new JdbcTemplate();
            ResultIterator ri = jt.executeQuery(sqlNew, new Object[]{moduleCode});
            if (ri.hasNext()) {
                ResultRecord rr = (ResultRecord) ri.next();
                if ("edit".equals(action)) {
                    msd = msd.getModuleSetupDb(moduleCode);
                    String sql = "update visual_module_setup set list_field=?,query_field=?,list_field_width=?,list_field_order=?,is_use=?,name=?,nav_tag_name=?,nav_tag_url=?,nav_tag_order=?,sub_nav_tag_name=?,sub_nav_tag_url=?,sub_nav_tag_order=?,btn_name=?,btn_script=?,btn_order=?,url_list=?,list_field_link=?,scripts=?,view_show=?,view_edit=?,filter=?,btn_bclass=?,is_workLog=?,msg_prop=?,validate_prop=?,validate_msg=?,view_list=?,field_begin_date=?,field_end_date=?,field_name=?,field_desc=?,field_label=?,scale_default=?,scale_min=?,scale_max=?,url_edit=?,orderby=?,sort=?,op_link_name=?,op_link_url=?,op_link_order=?,prompt_icon=?,prompt_field=?,prompt_cond=?,prompt_value=?,url_show=?,btn_edit_show=?,op_link_field=?,op_link_cond=?,op_link_value=?,op_link_event=?,btn_display_show=?,btn_add_show=?,description=?,btn_edit_display=?,btn_print_display=?,btn_role=?,btn_flow_show=?,btn_log_show=?,btn_del_show=?,op_link_role=?,cws_status=?,is_unit_show=?,unit_code=?,field_tree_show=?,field_tree_list=?,is_edit_inplace=?,module_code_log=?,add_to_url=?,list_field_show=?,is_auto_height=?,list_field_title=?,other_multi_ws=?,other_multi_order=?,prop_stat=?,export_word_view=?,module_tree_basic=?,module_tree_field_code=?,module_tree_field_name=?,page_setup=?,css=? where code=?";
                    try {
                        JdbcTemplate jdbcTemplate = new JdbcTemplate();
                        re = jdbcTemplate.executeUpdate(sql, new Object[]{
                                rr.getString("list_field"),
                                rr.getString("query_field"),
                                rr.getString("list_field_width"),
                                rr.getString("list_field_order"),
                                rr.getString("is_use"),
                                rr.getString("name"),
                                rr.getString("nav_tag_name"),
                                rr.getString("nav_tag_url"),
                                rr.getString("nav_tag_order"),
                                rr.getString("sub_nav_tag_name"),
                                rr.getString("sub_nav_tag_url"),
                                rr.getString("sub_nav_tag_order"),
                                rr.getString("btn_name"),
                                rr.getString("btn_script"),
                                rr.getString("btn_order"),
                                rr.getString("url_list"),
                                rr.getString("list_field_link"),
                                rr.getString("scripts"),
                                rr.getString("view_show"),
                                rr.getString("view_edit"),
                                rr.getString("filter"),
                                rr.getString("btn_bclass"),
                                rr.getString("is_workLog"),
                                rr.getString("msg_prop"),
                                rr.getString("validate_prop"),
                                rr.getString("validate_msg"),
                                rr.getString("view_list"),
                                rr.getString("field_begin_date"),
                                rr.getString("field_end_date"),
                                rr.getString("field_name"),
                                rr.getString("field_desc"),
                                rr.getString("field_label"),
                                rr.getString("scale_default"),
                                rr.getString("scale_min"),
                                rr.getString("scale_max"),
                                rr.getString("url_edit"),
                                rr.getString("orderby"),
                                rr.getString("sort"),
                                rr.getString("op_link_name"),
                                rr.getString("op_link_url"),
                                rr.getString("op_link_order"),
                                rr.getString("prompt_icon"),
                                rr.getString("prompt_field"),
                                rr.getString("prompt_cond"),
                                rr.getString("prompt_value"),
                                rr.getString("url_show"),
                                rr.getString("btn_edit_show"),
                                rr.getString("op_link_field"),
                                rr.getString("op_link_cond"),
                                rr.getString("op_link_value"),
                                rr.getString("op_link_event"),
                                rr.getString("btn_display_show"),
                                rr.getString("btn_add_show"),
                                rr.getString("description"),
                                rr.getString("btn_edit_display"),
                                rr.getString("btn_print_display"),
                                rr.getString("btn_role"),
                                rr.getString("btn_flow_show"),
                                rr.getString("btn_log_show"),
                                rr.getString("btn_del_show"),
                                rr.getString("op_link_role"),
                                rr.getString("cws_status"),
                                rr.getString("is_unit_show"),
                                rr.getString("unit_code"),
                                rr.getString("field_tree_show"),
                                rr.getString("field_tree_list"),
                                rr.getString("is_edit_inplace"),
                                rr.getString("module_code_log"),
                                rr.getString("add_to_url"),
                                rr.getString("list_field_show"),
                                rr.getString("is_auto_height"),
                                rr.getString("list_field_title"),
                                rr.getString("other_multi_ws"),
                                rr.getString("other_multi_order"),
                                rr.getString("prop_stat"),
                                rr.getString("export_word_view"),
                                rr.getString("module_tree_basic"),
                                rr.getString("module_tree_field_code"),
                                rr.getString("module_tree_field_name"),
                                rr.getString("page_setup"),
                                rr.getString("css"),
                                moduleCode
                        }) == 1;
                        if (re) {
                            PrimaryKey pk = msd.getPrimaryKey();
                            msd.refreshSave(pk);
                        }
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                } else if ("create".equals(action)) {
                    String sql = "insert into visual_module_setup (form_code,list_field,query_field,list_field_width,list_field_order,is_use,name,nav_tag_name,nav_tag_url,nav_tag_order,sub_nav_tag_name,sub_nav_tag_url,sub_nav_tag_order,btn_name,btn_script,btn_order,url_list,list_field_link,scripts,view_show,view_edit,filter,btn_bclass,is_workLog,msg_prop,validate_prop,validate_msg,view_list,field_begin_date,field_end_date,field_name,field_desc,field_label,scale_default,scale_min,scale_max,url_edit,orderby,sort,op_link_name,op_link_url,op_link_order,prompt_icon,prompt_field,prompt_cond,prompt_value,url_show,btn_edit_show,op_link_field,op_link_cond,op_link_value,op_link_event,btn_display_show,btn_add_show,description,btn_edit_display,btn_print_display,btn_role,btn_flow_show,btn_log_show,btn_del_show,op_link_role,cws_status,is_unit_show,unit_code,field_tree_show,field_tree_list,is_edit_inplace,module_code_log,add_to_url,list_field_show,is_auto_height,list_field_title,other_multi_ws,other_multi_order,code,prop_stat,export_word_view,module_tree_basic,module_tree_field_code,module_tree_field_name,page_setup,css) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    try {
                        JdbcTemplate jdbcTemplate = new JdbcTemplate();
                        re = jdbcTemplate.executeUpdate(sql, new Object[]{
                                rr.getString("form_code"),
                                rr.getString("list_field"),
                                rr.getString("query_field"),
                                rr.getString("list_field_width"),
                                rr.getString("list_field_order"),
                                rr.getString("is_use"),
                                rr.getString("name"),
                                rr.getString("nav_tag_name"),
                                rr.getString("nav_tag_url"),
                                rr.getString("nav_tag_order"),
                                rr.getString("sub_nav_tag_name"),
                                rr.getString("sub_nav_tag_url"),
                                rr.getString("sub_nav_tag_order"),
                                rr.getString("btn_name"),
                                rr.getString("btn_script"),
                                rr.getString("btn_order"),
                                rr.getString("url_list"),
                                rr.getString("list_field_link"),
                                rr.getString("scripts"),
                                rr.getString("view_show"),
                                rr.getString("view_edit"),
                                rr.getString("filter"),
                                rr.getString("btn_bclass"),
                                rr.getString("is_workLog"),
                                rr.getString("msg_prop"),
                                rr.getString("validate_prop"),
                                rr.getString("validate_msg"),
                                rr.getString("view_list"),
                                rr.getString("field_begin_date"),
                                rr.getString("field_end_date"),
                                rr.getString("field_name"),
                                rr.getString("field_desc"),
                                rr.getString("field_label"),
                                rr.getString("scale_default"),
                                rr.getString("scale_min"),
                                rr.getString("scale_max"),
                                rr.getString("url_edit"),
                                rr.getString("orderby"),
                                rr.getString("sort"),
                                rr.getString("op_link_name"),
                                rr.getString("op_link_url"),
                                rr.getString("op_link_order"),
                                rr.getString("prompt_icon"),
                                rr.getString("prompt_field"),
                                rr.getString("prompt_cond"),
                                rr.getString("prompt_value"),
                                rr.getString("url_show"),
                                rr.getString("btn_edit_show"),
                                rr.getString("op_link_field"),
                                rr.getString("op_link_cond"),
                                rr.getString("op_link_value"),
                                rr.getString("op_link_event"),
                                rr.getString("btn_display_show"),
                                rr.getString("btn_add_show"),
                                rr.getString("description"),
                                rr.getString("btn_edit_display"),
                                rr.getString("btn_print_display"),
                                rr.getString("btn_role"),
                                rr.getString("btn_flow_show"),
                                rr.getString("btn_log_show"),
                                rr.getString("btn_del_show"),
                                rr.getString("op_link_role"),
                                rr.getString("cws_status"),
                                rr.getString("is_unit_show"),
                                rr.getString("unit_code"),
                                rr.getString("field_tree_show"),
                                rr.getString("field_tree_list"),
                                rr.getString("is_edit_inplace"),
                                rr.getString("module_code_log"),
                                rr.getString("add_to_url"),
                                rr.getString("list_field_show"),
                                rr.getString("is_auto_height"),
                                rr.getString("list_field_title"),
                                rr.getString("other_multi_ws"),
                                rr.getString("other_multi_order"),
                                moduleCode,
                                rr.getString("prop_stat"),
                                rr.getString("export_word_view"),
                                rr.getString("module_tree_basic"),
                                rr.getString("module_tree_field_code"),
                                rr.getString("module_tree_field_name"),
                                rr.getString("page_setup"),
                                rr.getString("css")
                        }) == 1;
                        if (re) {
                            msd.refreshCreate();
                        }
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
            }
            json.put("ret", 1);
            json.put("msg", "操作成功");
            return json.toString();
        }
        return "th/setup/upgrade_module_by_db";
    }

    @RequestMapping(value = "/dbDict", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String dbDict(Model model) throws ValidateException, SQLException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }
        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));

        String op = ParamUtil.get(request, "op");
        boolean isShowField = ParamUtil.getBoolean(request, "isShowField", false);
        boolean isFilterForm = ParamUtil.getBoolean(request, "isFilterForm", false);
        model.addAttribute("appName", Global.AppName);
        model.addAttribute("server", Global.server);
        model.addAttribute("op", op);
        model.addAttribute("isShowField", isShowField);
        model.addAttribute("isFilterForm", isFilterForm);

        cn.js.fan.web.Config cfg = new cn.js.fan.web.Config();
        Iterator ir = cfg.getDBInfos().iterator();
        List<String> diInfo = new ArrayList<>();
        // 数据源
        while (ir.hasNext()) {
            DBInfo di = (DBInfo) ir.next();
            diInfo.add(di.name);
            diInfo.add(di.isDefault ? "selected" : "");
        }
        model.addAttribute("diInfo", cfg.getDBInfos());
        model.addAttribute("diInfo", cfg.getDBInfos());
        model.addAttribute("diInfo", cfg.getDBInfos());
        String dbSource = ParamUtil.get(request, "dbSource");
        model.addAttribute("dbSource", dbSource);

        if ("".equals(dbSource)) {
            dbSource = Global.getDefaultDB();
        }
        TransmitData td = new TransmitData();
        String connName = dbSource;
        Conn connTable = new Conn(connName);
        ResultSet rsTable = null;
        try {
            rsTable = td.getTableNames(connTable.getCon());
        } catch (Exception e) {
            model.addAttribute("msg", "数据库连接错误！");
            return "th/error/error";
        } finally {
            connTable.close();
        }

        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);

        // 取出数据字典置于map中
        Map mapDict = new HashMap();
        String sql = "select id,name,title from ft_data_dict_table order by id asc";
        ResultIterator riTable = jt.executeQuery(sql);
        while (riTable.hasNext()) {
            ResultRecord rrTable = riTable.next();
            long tableId = rrTable.getLong(1);
            String tableName = rrTable.getString("name");
            String title = StrUtil.getNullStr(rrTable.getString("title"));
            sql = "select id,name,title,def,len,nullable,data_type,is_autoincrement,remarks from ft_data_dict_column where cws_id=?";
            ResultIterator ri = jt.executeQuery(sql, new Object[]{tableId});
            mapDict.put(tableName, new Object[]{tableId, title, ri});
        }

        JSONArray tableInfo = new JSONArray();
        Vector<String> vNav = new Vector<>(); // 导航
        int n = 0;
        Conn conn = new Conn(dbSource);
        String tableName = "";
        while (rsTable.next()) {
            tableName = rsTable.getObject(3).toString();
            if (isFilterForm) {
                if (tableName.startsWith("ft_")) {
                    continue;
                }
            }

            int isDiff = 0; // 表格是否有差异
            n++;
            String tableTitle = "";
            boolean isNew = true;
            long tableId = -1;

            Object[] aryTable = (Object[]) mapDict.get(tableName);
            if (aryTable != null) {
                isNew = false;
                tableId = ((Long) aryTable[0]).longValue();
                String tit = (String) aryTable[1];
                if ("".equals(tit)) {
                    tableTitle = "[名称未填]";
                    isDiff = 1;
                } else {
                    tableTitle = tit;
                }
            } else {
                isDiff = 2;
                tableTitle = "新增表格";
            }
            // 表单循环显示
            JSONObject json = new JSONObject();
            json.put("tableName", tableName);
            json.put("tableTitle", tableTitle);
            json.put("index", n);
            json.put("isNew",isNew ? "diff" : "");
            tableInfo.add(json);

            // 如果数据字典中已存在该表，则取出其所有的字段信息置于mapCol中
            Map mapCol = new HashedMap();
            if (tableId != -1) {
                ResultIterator ri = (ResultIterator) aryTable[2];
                while (ri.hasNext()) {
                    ResultRecord rr = ri.next();
                    mapCol.put(rr.getString("name").toLowerCase(), rr);
                }
            }

            JSONArray columnInfo = new JSONArray();
            json.put("columnInfo", columnInfo);

            //疑问修改处====================================
            java.sql.Connection con_new = conn.getCon();
            DatabaseMetaData dmd = con_new.getMetaData();
            // mysql-connector-java 6.0以下用这个方法，高版本用此方法会取出所有的库中的所有的表
            ResultSet rsColumn = dmd.getColumns(con_new.getCatalog(), con_new.getSchema(), tableName, null);
            while (rsColumn.next()) {
                String columnName = rsColumn.getObject(4).toString().toLowerCase();
                String def = StrUtil.getNullStr(rsColumn.getString("COLUMN_DEF")); // 默认值
                int dataType = rsColumn.getInt("DATA_TYPE");
                String type = DataDictService.getDataType(dataType);
                int columnSize = rsColumn.getInt("COLUMN_SIZE");
                boolean isColNew = true; // 列是否在字典中有记录
                String clsSize = "";
                String tipSize = "";
                int dbColumnSize = 0;
                String dbTitle = "";
                if (tableId != -1) {
                    String dbType = "varchar";
                    ResultRecord rr = (ResultRecord) mapCol.get(columnName);
                    if (rr != null) {
                        isColNew = false;
                        dbType = rr.getString("data_type");
                        dbTitle = StrUtil.getNullStr(rr.getString("title"));
                        dbColumnSize = rr.getInt("len");
                        if (dbColumnSize != columnSize) {
                            isDiff = 4;
                            tipSize = "title='原长度为" + dbColumnSize + "'";
                            clsSize = "class='diff'";
                        }
                        if ("".equals(dbTitle)) {
                            isDiff = 5;
                        }
                    }
                }
                String clsTr = "";
                if (!isNew && isColNew) {
                    clsTr = "class='tr-new'";
                    isDiff = 6;
                }
                JSONObject jsonarray = new JSONObject();
                jsonarray.put("clsTr", clsTr);
                jsonarray.put("columnNameToLowerCase", columnName.toLowerCase());
                jsonarray.put("columnName", columnName);
                jsonarray.put("dbTitle", dbTitle);
                String tdTitleEmpty = dbTitle.equals("") ? "td-title-empty" : "";
                jsonarray.put("tdTitleEmpty",  tdTitleEmpty);
                jsonarray.put("type", type);
                jsonarray.put("clsSize", clsSize);
                jsonarray.put("tipSize", tipSize);
                jsonarray.put("columnSize", columnSize);
                jsonarray.put("def", def);
                jsonarray.put("remarks", rsColumn.getString("REMARKS"));
                jsonarray.put("nullAble", rsColumn.getInt("NULLABLE")==1? "是":"否");
                jsonarray.put("isAutoIncrement", rsColumn.getString("IS_AUTOINCREMENT").equals("YES")?"是":"否");
                columnInfo.add(jsonarray);
            }
            vNav.addElement(tableName + "," + isDiff);
        }
        model.addAttribute("tableInfo", tableInfo);

        conn.close();
        jt.close();

        if (isShowField) {
            StringBuffer sbNav = new StringBuffer();
            sbNav.append("<div id='nav'>");
            Iterator<String> irNav = vNav.iterator();
            n = 1;
            while (irNav.hasNext()) {
                String tbNameDesc = irNav.next();
                String[] ary = StrUtil.split(tbNameDesc, ",");
                String tbName = ary[0];
                int isDiff = StrUtil.toInt(ary[1], 0);
                String cls = isDiff == 0 ? "" : "is-diff"; // 如果表格或字段有差异
                sbNav.append("<div class=" + cls + ">" + n + "、<a href='#" + tbName + "'>");
                sbNav.append(tbName);
                sbNav.append("</a></div>");
                n++;
            }
            sbNav.append("</div>");
            model.addAttribute("sbNav", sbNav.toString());
        }
        return "th/setup/db_dict";
    }

    @RequestMapping(value = "/dbDiff", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String dbDiff(Model model) throws ValidateException, SQLException, ProxoolException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }
        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));
        model.addAttribute("appName", Global.AppName);
        model.addAttribute("server", Global.server);

        cn.js.fan.web.Config cfg = new cn.js.fan.web.Config();
        Iterator ir = cfg.getDBInfos().iterator();
        List<String> diInfo = new ArrayList<>();
        while (ir.hasNext()) {
            DBInfo di = (DBInfo) ir.next();
            diInfo.add(di.name);
            diInfo.add(di.isDefault ? "selected" : "");
        }
        model.addAttribute("diInfo", cfg.getDBInfos());

        String dbSource = ParamUtil.get(request, "dbSource");
        model.addAttribute("dbSource", dbSource);

        Boolean isDbSource = "".equals(dbSource);
        model.addAttribute("isDbSource", isDbSource);
        if (!"".equals(dbSource)){
            String op = ParamUtil.get(request, "op");
            if (op.equals("createTable")) {
                String tableName = ParamUtil.get(request, "tableName");
                createTable(request, tableName, dbSource);
                return "表：" + tableName + " 创建成功！<br/>";
            } else if (op.equals("addColumn")) {
                // ALTER TABLE `visual_module_setup` ADD COLUMN `module_code_log` VARCHAR(45) NOT NULL DEFAULT 'module_log' AFTER `is_edit_inplace`;
                // "`" + ff.getName() + "` " + typeStr + " COMMENT " +
                // StrUtil.sqlstr(ff.getTitle());
                String tableName = ParamUtil.get(request, "tableName");
                String columnName = ParamUtil.get(request, "columnName");

                String columnType = ParamUtil.get(request, "columnType");
                int columnSize = ParamUtil.getInt(request, "columnSize");
                int nullable = ParamUtil.getInt(request, "nullable");
                String columnDef = ParamUtil.get(request, "columnDef");

                addColumn(request, tableName, columnName, columnType, columnSize, nullable, columnDef, dbSource);
                return "列：" + tableName + "." + columnName + " 添加成功！<br/>";
            } else if (op.equals("alterColumn")) {
                String tableName = ParamUtil.get(request, "tableName");
                String columnName = ParamUtil.get(request, "columnName");

                String columnType = ParamUtil.get(request, "columnType");
                int columnSize = ParamUtil.getInt(request, "columnSize");
                int nullable = ParamUtil.getInt(request, "nullable");
                String columnDef = ParamUtil.get(request, "columnDef");
                alterColumn(request, tableName, columnName, columnType, columnSize, nullable, columnDef, dbSource);
                return "列：" + tableName + "." + columnName + " 修改成功！<br/>";
            } else if (op.equals("alterFormTable")) {
                // 添加系统定义的列
                String database = "";
                String sqlDb = "select database()";
                JdbcTemplate jt = new JdbcTemplate(new DataSource(dbSource));
                ResultIterator riDb = jt.executeQuery(sqlDb);
                if (riDb.hasNext()) {
                    ResultRecord rr = (ResultRecord) riDb.next();
                    database = rr.getString(1);
                }

                // 旧系统版本
                double version = ParamUtil.getDouble(request, "version", 4);
                sqlDb = "SELECT * FROM oa_sys_ver";
                riDb = jt.executeQuery(sqlDb);
                if (riDb.hasNext()) {
                    ResultRecord rr = (ResultRecord) riDb.next();
                    String ver = rr.getString("version");
                    version = StrUtil.toDouble(ver, 5);
                }
                ServletContext application = request.getSession().getServletContext();
                String realPath = application.getRealPath("/");
                if (realPath.lastIndexOf("/") != realPath.length() - 1) {
                    realPath += "/";
                }
                org.logicalcobwebs.proxool.ProxoolFacade.removeAllConnectionPools(5000); //
                org.logicalcobwebs.proxool.configuration.JAXPConfigurator.configure(realPath + "WEB-INF/proxool.xml", false);

                if (version < 3) {
                    // 添加拉单及冲抵字段
                    String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + database + "'";
                    ResultIterator ri = jt.executeQuery(sql);
                    while (ri.hasNext()) {
                        ResultRecord rr = (ResultRecord) ri.next();
                        String tableName = rr.getString(1).toLowerCase();
                        if (tableName.startsWith("ft_")) {
                            sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `cws_flag` TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `cws_status`, ADD COLUMN `cws_quote_id` INTEGER UNSIGNED AFTER `cws_flag`";
                            try {
                                jt.executeUpdate(sql);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }

                            sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `cws_progress` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `cws_status`";
                            try {
                                jt.executeUpdate(sql);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
                if (version < 4.0) {
                    // 添加cws_parent_form抵字段
                    String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + database + "'";
                    ResultIterator ri = jt.executeQuery(sql);
                    while (ri.hasNext()) {
                        ResultRecord rr = (ResultRecord) ri.next();
                        String tableName = rr.getString(1).toLowerCase();
                        if (tableName.startsWith("ft_")) {
                            // sql = "ALTER TABLE `" + tableName + "` DROP COLUMN `cws_parent_form`";
                            sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `cws_parent_form` varchar(20) AFTER `cws_status`";
                            try {
                                jt.executeUpdate(sql);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }

                if (version < 5.0) {
                    // 添加cws_create_date cws_modify_date cws_finish_date抵字段
                    String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + database + "'";
                    ResultIterator ri = jt.executeQuery(sql);
                    while (ri.hasNext()) {
                        ResultRecord rr = (ResultRecord) ri.next();
                        String tableName = rr.getString(1).toLowerCase();
                        if (tableName.startsWith("ft_") && !tableName.endsWith("_log")) {
                            sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `cws_create_date` datetime AFTER `cws_status`,  ADD COLUMN `cws_modify_date` datetime AFTER `cws_status`,  ADD COLUMN `cws_finish_date` datetime AFTER `cws_status`";
                            try {
                                jt.executeUpdate(sql);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }

                if (version < 6.0) {
                    // 添加cws_quote_form
                    String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + database + "'";
                    ResultIterator ri = jt.executeQuery(sql);
                    while (ri.hasNext()) {
                        ResultRecord rr = (ResultRecord) ri.next();
                        String tableName = rr.getString(1).toLowerCase();
                        if (tableName.startsWith("ft_") && !tableName.endsWith("_log")) {
                            sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `cws_quote_form` varchar(20) AFTER `cws_status`";
                            try {
                                jt.executeUpdate(sql);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }

                if (version < 3.0) {
                    // 因嵌套表格2控件的version改为了2，所以需处理一下
                    String sql = "update form_field set description=defaultValue where description='' and macroType='nest_sheet'";
                    try {
                        jt.executeUpdate(sql);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }

            TransmitData td = new TransmitData();
            String connName = dbSource; // 旧数据库
            String connName2 = Global.getDefaultDB(); // 本机为新数据库

            Conn conn = new Conn(connName);
            java.sql.Connection con = conn.getCon();

            Conn conn2 = new Conn(connName2);
            java.sql.Connection con2 = conn2.getCon();

            ResultSet rs_table_old = null;
            ResultSet rs_table_new = null;
            try {
                rs_table_old = td.getTableNames(con);
            } catch (Exception e) {
                e.printStackTrace();
                return "数据库连接错误！";
            }
            rs_table_new = td.getTableNames(con2);

            ResultSet rs_column_old = null;
            ResultSet rs_column_new = null;
            String table_old = "";
            String table_new = "";
            String column_old = "";
            String column_new = "";
            String add_table = "";
            String del_table = "";
            String add_column = "";
            String change_column = "";
            String del_column = "";
            boolean table_dif = true;
            boolean column_dif = true;

            int n = 0;
            while (rs_table_old.next()) {
                table_dif = true;
                table_old = rs_table_old.getObject(3).toString();
                n++;
                if (true) {
                    // continue;
                }
                while (rs_table_new.next()) {
                    table_new = rs_table_new.getObject(3).toString();
                    if (table_old.equals(table_new)) {
                        Conn conn_old = new Conn(connName);
                        java.sql.Connection con_old = conn_old.getCon();
                        DatabaseMetaData dmd_old = con_old.getMetaData();
                        // 在比对时，发现有些删除的表还有残留在mysql的data目录下
                        try {
                            // mysql-connector-java 6.0以下用这个方法
                            // rs_column_old = dmd_old.getColumns(null, null, table_old, null);
                            // 高版本用此方法
                            rs_column_old = dmd_old.getColumns(con_old.getCatalog(), con_old.getCatalog(), table_old, "%");
                        } catch (SQLException ex) {
                            return "数据库中表" + table_old + "不存在，可能在数据库的目录：data/数据库名称 下仍存在有残余文件！";
                        }

                        Conn conn_new = new Conn(connName2);
                        java.sql.Connection con_new = conn_new.getCon();
                        DatabaseMetaData dmd_new = con_new.getMetaData();
                        // mysql-connector-java 6.0以下用这个方法
                        // rs_column_new = dmd_new.getColumns(null, null, rs_table_new.getObject(3).toString(), null);
                        // 高版本用此方法
                        rs_column_new = dmd_new.getColumns(con_new.getCatalog(), con_new.getCatalog(), rs_table_new.getObject(3).toString(), "%");

                        // out.print("rs_table_new.getObject(3)=" + rs_table_new.getObject(3) + " rs_table_old.getObject(3)=" + rs_table_old.getObject(3) + "<BR>");

                        // rs_column_old = td.getColumns(rs_table_old.getObject(3).toString(),"forum");
                        // rs_column_new = td.getColumns(rs_table_new.getObject(3).toString(),"cwbbs");

                        while (rs_column_old.next()) {
                            column_dif = true;
                            column_old = rs_column_old.getObject(4).toString();
                            while (rs_column_new.next()) {
                                column_new = rs_column_new.getObject(4).toString();
                                // out.print("column_old=" + column_old + " column_new=" + column_new + "<BR>");
                                if (column_old.equals(column_new)) {
                                    // 比对长度
                                    if (rs_column_new.getInt("COLUMN_SIZE") != rs_column_old.getInt("COLUMN_SIZE")) {
                                        String columnTypeOld = rs_column_old.getString("TYPE_NAME");
                                        String columnType = rs_column_new.getString("TYPE_NAME");
                                        int columnSize = rs_column_new.getInt("COLUMN_SIZE");
                                        int digits = rs_column_new.getInt("DECIMAL_DIGITS");
                                        int nullable = rs_column_new.getInt("NULLABLE");
                                        String columnDef = rs_column_new.getString("COLUMN_DEF");

                                        boolean isFound = false;
                                        if (op.equals("alterColumnBatch")) {
                                            String[] tableColumns = ParamUtil.getParameters(request, "tableColumn");
                                            if (tableColumns != null) {
                                                for (int k = 0; k < tableColumns.length; k++) {
                                                    if ((table_new + "." + column_new).equals(tableColumns[k])) {
                                                        alterColumn(request, table_new, column_new, columnType, columnSize, nullable, columnDef, dbSource);
                                                        isFound = true;
                                                        return "列：" + table_new + "." + column_new + " 修改成功！<br/>";

                                                    }
                                                }
                                            }
                                        }

                                        if (!isFound) {
                                            change_column += "<input type='checkbox' name='tableColumn' value='" + table_old + "." + column_old + "'/>" + table_old + "." + column_old + "&nbsp;oldType=" + columnTypeOld + "&nbsp;newType=" + columnType + "&nbsp;size=" + rs_column_old.getObject("COLUMN_SIZE") + " new size=" + rs_column_new.getObject("COLUMN_SIZE") + "&nbsp;<a href='db_diff.jsp?op=alterColumn&dbSource=" + dbSource + "&tableName=" + table_new + "&columnName=" + column_new + "&columnSize=" + columnSize + "&nullable=" + nullable + "&columnDef=" + StrUtil.UrlEncode(columnDef) + "&columnType=" + columnType + "'>同步</a>";
                                            // change_column += "&nbsp;&nbsp;<a target='_blank' href='table_diff.jsp?tableA=" + table_new + "&tableB=" + table_new + "'>表格比较</a><BR/>";
                                            change_column += "<br/>";
                                        }
                                    }
                                    column_dif = false;
                                    break;
                                }
                            }
                            if (column_dif) {
                                del_column += table_old + "." + column_old + "<BR>";
                            }
                            rs_column_new.beforeFirst();
                        }

                        rs_column_old.beforeFirst();
                        rs_column_new.beforeFirst();
                        while (rs_column_new.next()) {
                            column_dif = true;
                            column_new = rs_column_new.getObject(4).toString();
                            while (rs_column_old.next()) {
                                column_old = rs_column_old.getObject(4).toString();
                                if (column_new.equals(column_old)) {
                                    column_dif = false;
                                    break;
                                }
                            }
                            if (column_dif) {
                                String columnType = rs_column_new.getString("TYPE_NAME");
                                int columnSize = rs_column_new.getInt("COLUMN_SIZE");
                                int digits = rs_column_new.getInt("DECIMAL_DIGITS");
                                int nullable = rs_column_new.getInt("NULLABLE");
                                String columnDef = rs_column_new.getString("COLUMN_DEF");

                                boolean isFound = false;
                                if (op.equals("addColumnBatch")) {
                                    String[] tableColumns = ParamUtil.getParameters(request, "tableColumn");
                                    if (tableColumns != null) {
                                        for (int k = 0; k < tableColumns.length; k++) {
                                            if ((table_new + "." + column_new).equals(tableColumns[k])) {
                                                addColumn(request, table_new, column_new, columnType, columnSize, nullable, columnDef, dbSource);
                                                isFound = true;
                                                return "列：" + table_new + "." + column_new + " 添加成功！<br/>";
                                            }
                                        }
                                    }
                                }
                                if (!isFound) {
                                    add_column += "<input type='checkbox' name='tableColumn' value='" + table_new + "." + column_new + "'/>" + table_new + "." + column_new + "&nbsp;&nbsp;类型：" + columnType + "&nbsp;默认值：" + columnDef + "&nbsp;允许空：" + nullable + "&nbsp;长度：" + columnSize + "&nbsp;<a href='db_diff.jsp?op=addColumn&dbSource=" + dbSource + "&tableName=" + table_new + "&columnName=" + column_new + "&columnSize=" + columnSize + "&nullable=" + nullable + "&columnDef=" + StrUtil.UrlEncode(columnDef) + "&columnType=" + columnType + "'>同步</a>";
                                    // add_column += "&nbsp;&nbsp;<a target='_blank' href='table_diff.jsp?tableA=" + table_new + "&tableB=" + table_new + "'>表格比较</a><BR/>";
                                    add_column += "<br/>";
                                }
                            }
                            rs_column_old.beforeFirst();
                        }

                        rs_column_old.close();
                        rs_column_new.close();

                        conn_old.close();
                        conn_new.close();

                        table_dif = false;
                        rs_table_new.beforeFirst();
                        break;
                    }
                }
                if (table_dif) {
                    del_table += table_old + "<BR>";
                }
                rs_table_new.beforeFirst();
            /*
            ResultSet rs_column = td.getColumns(rs_table_old.getObject(3).toString());
            while (rs_column.next()){
            out.print(rs_column.getObject(4));
            out.print("<br>");
            }
            */
            }

            rs_table_old.beforeFirst();
            rs_table_new.beforeFirst();

            while (rs_table_new.next()) {
                table_dif = true;
                table_new = rs_table_new.getObject(3).toString();
                while (rs_table_old.next()) {
                    table_old = rs_table_old.getObject(3).toString();
                    if (table_new.equals(table_old)) {
                        table_dif = false;
                        // rs_table_old.beforeFirst();
                        break;
                    }
                }
                if (table_dif) {
                    boolean isFound = false;
                    if (op.equals("createTableBatch")) {
                        String[] tableNames = ParamUtil.getParameters(request, "tableName");
                        if (tableNames != null) {
                            for (int k = 0; k < tableNames.length; k++) {
                                if ((table_new).equals(tableNames[k])) {
                                    createTable(request, table_new, dbSource);
                                    isFound = true;
                                    return "表：" + table_new + " 创建成功！<br/>";
                                }
                            }
                        }
                    }
                    if (!isFound) {
                        add_table += "<input type='checkbox' name='tableName' value='" + table_new + "'/>" + table_new + "&nbsp;&nbsp;&nbsp;&nbsp;<a href='db_diff.jsp?op=createTable&dbSource=" + dbSource + "&tableName=" + table_new + "'>增加</a><br>";
                    }
                }
                rs_table_old.beforeFirst();
            }
            conn.close();
            conn2.close();
        }
        return "th/setup/db_diff";
    }

    @RequestMapping(value = "/dbConnEdit", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public String dbConnEdit(Model model) throws ValidateException, SQLException, ProxoolException{
//        Privilege pvg = new Privilege();
//        if (Global.getInstance().isFormalOpen()) {
//            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
//                model.addAttribute("msg", "请以管理员身份登录以后再操作");
//                return "th/error/error";
//            }
//        }
        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));

        String oadb="", user="", pwd="", ip="", port="", database="", url="", maximum_connection_count="", odbcName = "", path="";
        String dbSource = ParamUtil.get(request, "dbSource");
        model.addAttribute("dbSource", dbSource);
        String op = ParamUtil.get(request, "op");
        model.addAttribute("op", op);
        boolean isValid = true;
        String className = "";
        String msg = "";
        try {
            ProxoolUtil proxoolUtil = SpringUtil.getBean(ProxoolUtil.class);
            Document doc = proxoolUtil.getDoc();
            Element root = doc.getRootElement();
            Element el = null;
            List list = root.getChildren("proxool");
            Iterator ir = list.iterator();
            while (ir.hasNext()) {
                Element e = (Element) ir.next();
                el = e.getChild("alias");
                if (el.getText().equals(dbSource)) {
                    el = e.getChild("driver-url");
                    url = el.getText();
                    el = e.getChild("driver-class");
                    className = el.getText();
                    el = e.getChild("maximum-connection-count");
                    maximum_connection_count = el.getText();
                    el = e.getChild("driver-properties");
                    Iterator irEl = el.getChildren("property").iterator();
                    while (irEl.hasNext()) {
                        Element ep = (Element) irEl.next();
                        String attrName = ep.getAttributeValue("name");
                        String attrVal = ep.getAttributeValue("value");
                        if (attrName.equals("user")) {
                            user = attrVal;
                        }
                        else {
                            pwd = attrVal;
                        }
                    }

                    if (url.startsWith("jdbc:odbc:driver")) {
                        oadb = "access";
                        int p = url.indexOf("{");
                        int q = url.indexOf("}");
                        odbcName = url.substring(p, q+1).trim();
                        p = url.lastIndexOf("=");
                        path = url.substring(p).trim();
                    }
                    else if (url.startsWith("jdbc:oracle:thin")) {
                        oadb = "Oracle";
                        int p = url.indexOf("@//") + 3;
                        int q = url.indexOf(":", p);
                        ip = url.substring(p, q);
                        p = url.indexOf("/", q);
                        port = url.substring(q+1, p);
                        database = url.substring(p + 1);
                    }
                    else if (url.startsWith("jdbc:microsoft:sqlserver")) {
                        oadb = "mssql";
                        int p = url.indexOf("//") + 2;
                        int q = url.indexOf(":", p);
                        ip = url.substring(p, q);
                        p = url.indexOf(";", q);
                        port = url.substring(q+1, p);
                        p = url.lastIndexOf("=");
                        database = url.substring(p+1);
                    }
                    else if (url.startsWith("jdbc:sqlserver")) {
                        oadb = "mssql_n";
                        int p = url.indexOf("//") + 2;
                        int q = url.indexOf(":", p);
                        ip = url.substring(p, q);
                        p = url.indexOf(";", q);
                        port = url.substring(q+1, p);
                        p = url.lastIndexOf("=");
                        database = url.substring(p+1);
                    }
                    else if (url.startsWith("jdbc:mysql")) {
                        oadb = "mysql";
                        int p = url.indexOf("//") + 2;
                        int q = url.indexOf(":", p);
                        ip = url.substring(p, q);
                        p = url.indexOf("/", q);
                        port = url.substring(q+1, p);
                        q = url.indexOf("?");
                        database = url.substring(p+1, q);
                    }
                    else if (url.startsWith("jdbc:postgresql")) {
                        oadb = "postgresql";
                        int p = url.indexOf("//") + 2;
                        int q = url.indexOf(":", p);
                        ip = url.substring(p, q);
                        p = url.indexOf("/", q);
                        port = url.substring(q+1, p);
                        database = url.substring(p + 1);
                    }
                    break;
                }
            }
            model.addAttribute("oadb", oadb);
            model.addAttribute("odbcName", odbcName);
            model.addAttribute("path", path);
            model.addAttribute("ip", ip);
            model.addAttribute("port", port);
            model.addAttribute("database", database);
            model.addAttribute("maximumConnectionCount", maximum_connection_count);
            model.addAttribute("user", user);
            model.addAttribute("pwd", pwd);
        } finally {
         }
        return "th/setup/db_conn_edit";
    }




    @RequestMapping(value = "/configDingding", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public String configDingding(Model model) throws ValidateException, SQLException, ProxoolException {
        Privilege pvg = new Privilege();
        if (!pvg.isUserPrivValid(request, PrivDb.PRIV_ADMIN)) {
            model.addAttribute("msg", "请以管理员身份登录以后再操作");
            return "th/error/error";
        }
        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));

        JSONArray jsonArray = new JSONArray();
        JSONArray jsonArray2 = new JSONArray();
        JSONArray jsonArray3 = new JSONArray();
        com.redmoon.dingding.Config dingdingCfg = com.redmoon.dingding.Config.getInstance();
        Element root = dingdingCfg.getRoot();

        String name = "", value = "";
        name = request.getParameter("name");
        if (name != null && !name.equals("")) {
            value = ParamUtil.get(request, "value");
            dingdingCfg.setProperty(name, value);

            com.redmoon.oa.Config.reload();
            String msg = LocalUtil.LoadString(request, "res.common", "info_op_success");

            //out.println(StrUtil.jAlert_Redirect(SkinUtil.LoadString(request, "info_op_success"), "提示", "config_dingding.jsp"));
            model.addAttribute("msg", msg);
            return "th/error/error";

            //return;
        }

        int k = 0;
        Iterator ir = root.getChildren().iterator();
        String desc = "";
        while (ir.hasNext()) {
            JSONObject jsonObject = new JSONObject();
            Element e = (Element) ir.next();
            name = e.getName();
            if (name.equals("agentMenu")) {
                continue;
            }
            String isDisplay = StrUtil.getNullStr(e.getAttributeValue("isDisplay"));
            if (isDisplay.equals("false")) {
                continue;
            }
            jsonObject.put("name",name);
            jsonObject.put("type1",false);
            jsonObject.put("type2",false);
            jsonObject.put("type3",false);
//            jsonObject.put("type4",false);
//            jsonObject.put("type5",false);

            value = e.getValue();
            desc = (String) e.getAttributeValue("desc");
            jsonObject.put("desc",desc);
            jsonObject.put("value",value);

            if (!"isSyncDingDingToOA".equals(name) && (value.equals("true") || value.equals("false"))) {
                jsonObject.put("type1",true);
                jsonObject.put("value",value);
            }else {
                jsonObject.put("type2",true);
                String opts = StrUtil.getNullStr(e.getAttributeValue("options"));
                if ("".equals(opts)) {

                    jsonObject.put("type3",true);
                    jsonObject.put("value",value);

                }else {
                    //jsonObject.put("type4",true);
                    String[] ary = StrUtil.split(opts, ",");
                    for (String item : ary) {
                        JSONObject jsonAryOpts1 = new JSONObject();
                        JSONObject jsonAryOpts2 = new JSONObject();
                        String[] aryOpts = StrUtil.split(item, "\\|");
                        if (aryOpts != null && aryOpts.length == 2) {
                            if("true".equals(value) ||  "false".equals(value)) {
                                if ("true".equals(aryOpts[0]) || "false".equals(aryOpts[0])){
                                    jsonAryOpts1.put("aryOpts0", aryOpts[0]);
                                    jsonAryOpts1.put("aryOpts1", aryOpts[1]);
    //                              jsonObject.put("aryOpts",jsonAryOpts);
                                    jsonArray2.add(jsonAryOpts1);
                                }

                            }else{
                                if (!"true".equals(aryOpts[0]) && !"false".equals(aryOpts[0])){
                                    jsonAryOpts2.put("aryOpts0", aryOpts[0]);
                                    jsonAryOpts2.put("aryOpts1", aryOpts[1]);
                                //  jsonObject.put("aryOpts",jsonAryOpts);
                                    jsonArray3.add(jsonAryOpts2);
                                }
                            }

                        }
                    }
                    jsonObject.put("value",value);
                }

            }
            jsonObject.put("aryOpts1",jsonArray2);
            jsonObject.put("aryOpts2",jsonArray3);
            jsonObject.put("k",k);
            jsonObject.put("attr","#attr"+k);
            k++;

            jsonArray.add(jsonObject);
            System.out.println("jsonObject: "+jsonObject);
        }

        System.out.println("jsonArray: "+jsonArray);
        model.addAttribute("arys", jsonArray);
        return "th/config/config_dingding";
    }

    @RequestMapping(value = "/configWeixin", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public String configWeixin(Model model) throws ValidateException, SQLException, ProxoolException {
        Privilege pvg = new Privilege();
        if (!pvg.isUserPrivValid(request, PrivDb.PRIV_ADMIN)) {
            model.addAttribute("msg", "请以管理员身份登录以后再操作");
            return "th/error/error";
        }
        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));
        JSONArray jsonArray1 = new JSONArray();
        JSONArray jsonArray2 = new JSONArray();
        JSONArray jsonArray3 = new JSONArray();
        JSONArray jsonArray4 = new JSONArray();

        com.redmoon.weixin.Config weixinCfg = com.redmoon.weixin.Config.getInstance();
        Element root = weixinCfg.getRoot();

        int k = 0;
        Iterator ir = root.getChildren().iterator();
        String desc = "";
        while (ir.hasNext()) {
            JSONObject jsonObject1 = new JSONObject();
            Element e = (Element) ir.next();
            String name = e.getName();
            if ("agentMenu".equals(name)) {
                continue;
            }

            String isDisplay = StrUtil.getNullStr(e.getAttributeValue("isDisplay"));
            if ("false".equals(isDisplay)) {
                continue;
            }

            String value = e.getValue();
            //jsonObject1.put("value",value);
            jsonObject1.put("name",name);
            desc = e.getAttributeValue("desc");
            jsonObject1.put("desc",desc);

            jsonObject1.put("type1",false);
            jsonObject1.put("type2",false);
            jsonObject1.put("type3",false);
            jsonObject1.put("type4",false);
            jsonObject1.put("type5",false);

            if (!"isSyncWxToOA".equals(name) && !"isUserIdUseMobile".equals(name) && (value.equals("true") || value.equals("false"))) {
                jsonObject1.put("type1",true);
                jsonObject1.put("value",value);
            }else if (name.equals("isUserIdUseMobile")) {
                jsonObject1.put("type2",true);
                String relateVal = "1";
                if (weixinCfg.isUserIdUseEmail()) {
                    relateVal = "2";
                }
                else if (weixinCfg.isUserIdUseMobile()) {
                    relateVal = "3";
                }
                else if (weixinCfg.isUserIdUseAccount()) {
                    relateVal = "4";
                }
                jsonObject1.put("relateVal",relateVal);
            }else{
                jsonObject1.put("type3",true);
                String opts = StrUtil.getNullStr(e.getAttributeValue("options"));
                if ("".equals(opts)) {
                    jsonObject1.put("type4",true);
                    jsonObject1.put("value",value);
                }else{
                    String[] ary = StrUtil.split(opts, ",");
                    for (String item : ary) {
                        JSONObject jsonAryOpts1 = new JSONObject();
                        JSONObject jsonAryOpts2 = new JSONObject();
                        String[] aryOpts = StrUtil.split(item, "\\|");
                        if (aryOpts != null && aryOpts.length == 2) {
                            if("true".equals(value) ||  "false".equals(value)) {
                                if ("true".equals(aryOpts[0]) || "false".equals(aryOpts[0])){
                                    jsonAryOpts1.put("aryOpts0", aryOpts[0]);
                                    jsonAryOpts1.put("aryOpts1", aryOpts[1]);
                                    //jsonObject.put("aryOpts",jsonAryOpts);
                                    jsonArray3.add(jsonAryOpts1);
                                }

                            }else{
                                if (!"true".equals(aryOpts[0]) && !"false".equals(aryOpts[0])){
                                    jsonAryOpts2.put("aryOpts0", aryOpts[0]);
                                    jsonAryOpts2.put("aryOpts1", aryOpts[1]);
                                    //jsonObject.put("aryOpts",jsonAryOpts);
                                    jsonArray4.add(jsonAryOpts2);
                                }
                            }

                        }
                    }
                    jsonObject1.put("value",value);
                }
            }
            jsonObject1.put("aryOpts1",jsonArray3);
            jsonObject1.put("aryOpts2",jsonArray4);
            jsonObject1.put("k",k);
            jsonObject1.put("formNumber","form"+k);
            jsonObject1.put("attr","#attr"+k);
            k++;

            System.out.println(jsonObject1);
            jsonArray1.add(jsonObject1);
        }

        Element agentMenu = root.getChild("agentMenu");
        List<Element> menus = agentMenu.getChildren("item");
        for (Element ele : menus) {
            JSONObject jsonObject2 = new JSONObject();
            String agentId = ele.getChild("agentId").getText();
            String agentName = ele.getChild("agentName").getText();
            String logo = ele.getChild("logo").getText();
            String secret = ele.getChild("secret").getText();
            String homeUrl = ele.getChild("homeUrl").getText();
            jsonObject2.put("agentId",agentId);
            jsonObject2.put("agentName",agentName);
            jsonObject2.put("logo",logo);
            jsonObject2.put("secret",secret);
            jsonObject2.put("homeUrl",homeUrl);

            jsonObject2.put("formNumber","form"+agentId);
            String text = "";
            if (!"".equals(logo)) {
                jsonObject2.put("text","LOGO已设置，可以设置应用信息");
            } else {
                jsonObject2.put("text","请选择图片，需小于1M，类型为jpg或png");
            }
            jsonArray2.add(jsonObject2);
        }

        model.addAttribute("ary1s", jsonArray1);
        model.addAttribute("ary2s", jsonArray2);
        return "th/config/config_weixin";
    }


    @RequestMapping(value = "/dbConnEditBtn", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    @ResponseBody
    public String dbConnEditBtn() throws ProxoolException, ValidateException, SQLException, ProxoolException{
        String oadb = "", user = "", pwd = "", ip = "", port = "", database = "", url = "", maximum_connection_count = "", odbcName = "", path = "";

        String dbSource = ParamUtil.get(request, "dbSource");
        String op = ParamUtil.get(request, "op");
        boolean isValid = true;
        String className = "";
        String msg = "";

        if (op.equals("setup")) {
            String name = ParamUtil.get(request, "name");
            user = ParamUtil.get(request, "user");
            pwd = ParamUtil.get(request, "pwd");
            ip = ParamUtil.get(request, "ip");
            port = ParamUtil.get(request, "port");
            database = ParamUtil.get(request, "database");
            oadb = ParamUtil.get(request, "oadb");
            odbcName = ParamUtil.get(request, "odbcName");
            path = ParamUtil.get(request, "path");

            int max_conn = ParamUtil.getInt(request, "max_conn", 200);

            if (oadb.equalsIgnoreCase("access")) {
                className = "sun.jdbc.odbc.JdbcOdbcDriver";
                url = "jdbc:odbc:driver={" + odbcName + "};DBQ=" + path;
            } else if (oadb.equalsIgnoreCase(Global.DB_ORACLE)) {
                className = "oracle.jdbc.driver.OracleDriver";
                url = "jdbc:oracle:thin:@//" + ip + ":" + port + "/" + database;
            } else if (oadb.equalsIgnoreCase("mssql")) {
                className = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
                url = "jdbc:microsoft:sqlserver://" + ip + ":" + port + ";DatabaseName=" + database;
            } else if (oadb.equalsIgnoreCase("mssql_n")) {
                className = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                url = "jdbc:sqlserver://" + ip + ":" + port + ";DatabaseName=" + database;
            } else if (oadb.equalsIgnoreCase(Global.DB_MYSQL)) {
                className = "com.mysql.cj.jdbc.Driver";
                url = "jdbc:mysql://" + ip + ":" + port + "/" + database + "?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=Asia/Shanghai";
            } else if (oadb.equalsIgnoreCase(Global.DB_POSTGRESQL)) {
                className = "org.postgresql.Driver";
                url = "jdbc:postgresql://" + ip + ":" + port + "/" + database;
            }

            try {
                Class.forName(className);
            } catch (java.lang.ClassNotFoundException e) {
                isValid = false;
                msg = e.getMessage();
            }
            try {
                java.sql.Connection con = DriverManager.getConnection(url, user, pwd);
                con.close();
            } catch (SQLException e) {
                isValid = false;
                msg = e.getMessage();
            }

            JSONObject json = new JSONObject();
            if (isValid) {
                try {
                    IConfigUtil configUtil = SpringUtil.getBean(IConfigUtil.class);
                    String xml = configUtil.getXml("config_sys");
                    SAXBuilder sb = new SAXBuilder();
                    Document doc = sb.build(new InputSource(new StringReader(xml)));

                    Element root = doc.getRootElement();
                    Element which = root.getChild("DataBase");
                    // 先删后加
                    List list = which.getChildren("db");
                    Iterator ir = list.iterator();
                    while (ir.hasNext()) {
                        Element e = (Element) ir.next();
                        if (e.getChildText("name").equals(name)) {
                            which.removeContent(e);
                            break;
                        }
                    }

                    Element edb = new Element("db");

                    Element e = new Element("name");
                    e.addContent(name);
                    edb.addContent(e);

                    e = new Element("Default");
                    e.addContent("false");
                    edb.addContent(e);

                    e = new Element("UsePool");
                    e.addContent("false");
                    edb.addContent(e);

                    e = new Element("PoolName");
                    e.addContent("");
                    edb.addContent(e);

                    e = new Element("DBDriver");
                    e.addContent("org.logicalcobwebs.proxool.ProxoolDriver");
                    edb.addContent(e);

                    e = new Element("ConnStr");
                    e.addContent("proxool." + name);
                    edb.addContent(e);

                    which.addContent(edb);

                    configUtil.putXml("config_sys", doc);
                } catch (JDOMException | IOException e) {
                    LogUtil.getLog(getClass()).error(e);
                }

                try {
                    ProxoolUtil proxoolUtil = SpringUtil.getBean(ProxoolUtil.class);
                    Document doc = proxoolUtil.getDoc();

                    Element root = doc.getRootElement();

                    // 先删后加
                    List list = root.getChildren();
                    Iterator ir = list.iterator();
                    while (ir.hasNext()) {
                        Element e = (Element) ir.next();
                        if (e.getChildText("alias").equals(name)) {
                            root.removeContent(e);
                            break;
                        }
                    }

                    Element edb = new Element("proxool");

                    Element e = new Element("alias");
                    e.addContent(name);
                    edb.addContent(e);

                    e = new Element("driver-url");
                    e.addContent(url);
                    edb.addContent(e);

                    e = new Element("driver-class");
                    e.addContent(className);
                    edb.addContent(e);

                    e = new Element("driver-properties");
                    Element el = new Element("property");
                    el.setAttribute("name", "user");
                    el.setAttribute("value", user);
                    e.addContent(el);
                    el = new Element("property");
                    el.setAttribute("name", "password");
                    el.setAttribute("value", pwd);
                    e.addContent(el);
                    edb.addContent(e);

                    e = new Element("maximum-connection-count");
                    e.addContent(String.valueOf(max_conn));
                    edb.addContent(e);

                    e = new Element("house-keeping-test-sql");
                    e.addContent("select 1");
                    edb.addContent(e);

                    root.addContent(edb);

                    proxoolUtil.write();

                    Global.getInstance().init();

                    org.logicalcobwebs.proxool.ProxoolFacade.removeAllConnectionPools(5000);
                    org.logicalcobwebs.proxool.configuration.JAXPConfigurator.configure(proxoolUtil.getCfgPath(), false);
                } finally {

                }
                json.put("ret", "1");
                json.put("msg", "连接成功！");
            } else {
                json.put("ret", "0");
                json.put("msg", msg);
            }
          return json.toString();
        }
        return "th/setup/db_conn_edit";
    }

    @RequestMapping(value = "/initRedmoonId", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public String initRedmoonId(Model model) throws ValidateException, SQLException, ProxoolException{
        model.addAttribute("skinPath", SkinMgr.getSkinPath(request, false));
        String op = request.getParameter("op");
        String tableName = request.getParameter("formName");
        String nowId = request.getParameter("nowId");
        int len = 95;
        String[][] tables = SysUtil.getSequenceOATables();
        if ("start".equals(op)) {
            int cmsId = 0; // 记录常量的值也就是redmoonid表idType的值
            int id = 0;    // 记录redmoonid表ID初始化后的值
            String sql = "";

            if (!"".equals(nowId)) { // 修
                int nowId2 = Integer.parseInt(nowId);
                JdbcTemplate jt = new JdbcTemplate();
                for (int i = 0; i < len; i++) {
                    if (tables[i][0].equals(tableName)) {
                        try {
                            sql = "update redmoonid set id=" + nowId2 + " where idType=" + i;
                            int r = jt.executeUpdate(sql);
                            model.addAttribute("msg", "操作成功");
                            return "th/error/error";
                        } catch (Exception e) {
                            e.printStackTrace();
                            LogUtil.getLog(getClass()).error(e);
                        }
                    }
                }
            } else if ("all".equals(tableName)) {
                SysUtil.initSequenceOATables();
                model.addAttribute("msg", "操作成功");
                return "th/error/error";
            } else {
                // 初始化
                int s = 0;
                String tableId = "id";
                for (int i = 0; i < len; i++) {
                    if (tables[i][0]!=null && tables[i][0].equals(tableName)) {
                        cmsId = i;
                        s++;
                        if (tables[i][2] != null) {
                            tableId = tables[i][2];
                        }
                    }
                }
                if (s == 0) {
                    model.addAttribute("msg", "表不存在");
                    return "th/error/error";
                }
                JdbcTemplate jt = new JdbcTemplate();
                try {
                    sql = "select max(" + tableId + ") from " + tableName;
                    ResultIterator ri = jt.executeQuery(sql);
                    if (ri.hasNext()) {
                        ResultRecord rd = (ResultRecord) ri.next();
                        id = rd.getInt(1);
                    }
                    id++;
                } catch (Exception e) {
                    e.printStackTrace();
                    LogUtil.getLog(getClass()).error(e);
                }

                try {
                    sql = "update redmoonid set id=" + id + " where idType=" + cmsId;
                    int r = jt.executeUpdate(sql);
                    if (r == 0) {
                        sql = "insert into redmoonid (id, idType) values (" + id + "," + cmsId + ")";
                        jt.executeUpdate(sql);
                    }
                    model.addAttribute("msg", "操作成功");
                    return "th/error/error";
                } catch (Exception e) {
                    e.printStackTrace();
                    LogUtil.getLog(getClass()).error(e);
                }
            }
        }
        SequenceManager.init();

        JSONArray tInfo = new JSONArray();
        for (int k = 0; k < len; k++) {
            int tId = 0;
            String tName = "";
            int mid = 0;
            int nid = 0;
            String tableId2 = "id";
            if (!StringUtils.isEmpty(tables[k][0])) {
                tName = tables[k][0];
                tId = k;
                if (tables[k][2] != null) {
                    tableId2 = tables[k][2];
                }
                String sql2 = "select max(" + tableId2 + ") from " + tName;
                // System.out.println(getClass() + " " + sql2);
                JdbcTemplate jtl = new JdbcTemplate();
                try {
                    ResultIterator rir = jtl.executeQuery(sql2);
                    if (rir.hasNext()) {
                        ResultRecord rsd = (ResultRecord) rir.next();
                        mid = rsd.getInt(1);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

                try {
                    sql2 = "select id from redmoonid where idType=" + k;
                    ResultIterator rir = jtl.executeQuery(sql2);
                    if (rir.hasNext()) {
                        ResultRecord rsd = (ResultRecord) rir.next();
                        nid = rsd.getInt(1);
                        //System.out.println(" nid= "+nid);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    LogUtil.getLog(getClass()).error(e);
                }
            } else {
                continue;
            }

            boolean isValid = true;
            if (nid < mid) {
                isValid = false;
            }
            String style = !isValid ? "style='color:red'" : "";
            JSONObject json = new JSONObject();
            json.put("tName", tName);
            json.put("k", k);
            json.put("style", style);
            json.put("mid", mid);
            json.put("nid", nid);
            tInfo.add(json);
        }
        model.addAttribute("tInfo", tInfo);
        return "th/setup/init_redmoon_id";
    }

    /*数据字典集合页面

     */
    @RequestMapping(value = "/dataDict", produces = {"text/html;charset=UTF-8;", "application/json;"})
    public String dataDict(Model model) {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                model.addAttribute("msg", "请以管理员身份登录以后再操作");
                return "th/error/error";
            }
        }
        return "th/setup/data_dict";
    }

    /**
     * 数据库比较工具，同步数据库中的表、字段
     * 以本应用为新版数据库，其它数据源为旧版数据库
     * 当点击“添加表单中的系统定义的列”时，需检查一下代码中针对version的判断条件
     */

    // 增加列
    public void addColumn(HttpServletRequest request, String tableName, String columnName, String columnType, int columnSize, int nullable, String columnDef, String dbSource) throws ErrMsgException, SQLException {
        // ALTER TABLE `visual_module_setup` ADD COLUMN `module_code_log` VARCHAR(45) NOT NULL DEFAULT 'module_log' AFTER `is_edit_inplace`;
        String sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `" + columnName + "` ";
        if ("TINYINT UNSIGNED".equals(columnType)) {
            sql += "TINYINT(" + columnSize + ") UNSIGNED";
        } else if ("INT UNSIGNED".equals(columnType)) {
            sql += "INT(" + columnSize + ") UNSIGNED";
        } else if ("BIGINT UNSIGNED".equals(columnType)) {
            sql += "BIGINT(" + columnSize + ") UNSIGNED";
        } else if ("DATE".equals(columnType) || "DATETIME".equals(columnType)) {
            sql += columnType;
        } else if ("DOUBLE".equals(columnType)) {
            sql += columnType;
        } else {
            sql += columnType + "(" + columnSize + ")";
        }
        if (nullable == 0) {
            sql += " NOT NULL";
        }
        if (columnDef != null && !"null".equals(columnDef) && !"".equals(columnDef)) {
            if ("BIT".equals(columnType)) {
                sql += " DEFAULT " + columnDef;
            } else {
                sql += " DEFAULT '" + columnDef + "'";
            }
        }
        // System.out.println(sql);
        LogUtil.getLog(getClass()).info(sql);
        try {
            JdbcTemplate jtOld = new JdbcTemplate(new DataSource(dbSource));
            jtOld.executeUpdate(sql);
        } catch (SQLException e) {
            LogUtil.getLog(getClass()).error(e);
        }
    }

    // 创建表
    public void createTable(HttpServletRequest request, String tableName, String dbSource) throws ErrMsgException, SQLException {
        JdbcTemplate jt = new JdbcTemplate();
        ResultIterator ri = jt.executeQuery("show create table " + tableName);
        if (ri.hasNext()) {
            ResultRecord rr = (ResultRecord) ri.next();
            String sql = rr.getString(2);
            try {
                JdbcTemplate jtOld = new JdbcTemplate(new DataSource(dbSource));
                jtOld.executeUpdate(sql);
            } catch (SQLException e) {
                LogUtil.getLog(getClass()).error(e);
            }
        }
    }

    // 更改列类型
    public void alterColumn(HttpServletRequest request, String tableName, String columnName, String columnType, int columnSize, int nullable, String columnDef, String dbSource) throws ErrMsgException, SQLException {
        String sql = "ALTER TABLE `" + tableName + "` MODIFY COLUMN `" + columnName + "` ";
        if (columnType.equals("TINYINT UNSIGNED")) {
            sql += "TINYINT(" + columnSize + ") UNSIGNED";
        } else if (columnType.equals("INT UNSIGNED")) {
            sql += "INT(" + columnSize + ") UNSIGNED";
        } else if (columnType.equals("BIGINT UNSIGNED")) {
            sql += "BIGINT(" + columnSize + ") UNSIGNED";
        } else if (columnType.equals("DATE") || columnType.equals("DATETIME")) {
            sql += columnType;
        } else if (columnType.equals("DOUBLE")) {
            sql += columnType;
        } else {
            sql += columnType + "(" + columnSize + ")";
        }
        if (nullable == 0) {
            sql += " NOT NULL";
        }
        if (columnDef != null && !columnDef.equals("null") && !columnDef.equals("")) {
            if (columnType.equals("BIT")) {
                sql += " DEFAULT " + columnDef;
            } else {
                sql += " DEFAULT '" + columnDef + "'";
            }
        }
        try {
            JdbcTemplate jtOld = new JdbcTemplate(new DataSource(dbSource));
            jtOld.executeUpdate(sql);
        } catch (SQLException e) {
            LogUtil.getLog(getClass()).error(e);
        }
    }


    /**
     * @return
     * @throws ValidateException
     */
    @RequestMapping(value = "/setupDbProp", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    @ResponseBody
    public String setupDbProp() throws ValidateException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                throw new ValidateException("请以管理员身份登录以后再操作");
            }
        }

        try {
            String user = ParamUtil.get(request, "user");
            String ip = ParamUtil.get(request, "ip");
            int port = ParamUtil.getInt(request, "port");
            String database = ParamUtil.get(request, "database");
            String pwd = ParamUtil.get(request, "pwd");
            // String url = "jdbc:mysql://" + ip + ":" + port + "/" + database + "?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=Asia/Shanghai";
            String url;
            if (Global.db.equals(Global.DB_POSTGRESQL)) {
                url = "jdbc:postgresql://" + ip + ":" + port + "/" + database;
            } else {
                url = "jdbc:mysql://" + ip + ":" + port + "/" + database + "?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false&connectTimeout=20000&socketTimeout=180000&serverTimezone=Asia/Shanghai";
            }
            int maximumConnectionCount = ParamUtil.getInt(request, "maximum_connection_count");

            /*
            // 用Properties保存会破坏原来的格式，可读性差，故改为PropertiesConfiguration
            Properties properties = new Properties();
            try {
                properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("application.properties"));
            } catch (IOException e) {
                LogUtil.getLog(getClass()).error(e);
            }
            properties.setProperty("spring.datasource.url", url);
            properties.setProperty("spring.datasource.username", user);
            properties.setProperty("spring.datasource.password", pwd);
            properties.setProperty("spring.datasource.maxActive", String.valueOf(maximumConnectionCount));
            URL confURL = getClass().getResource("/application.properties");
            String xmlpath = confURL.getFile();
            xmlpath = URLDecoder.decode(xmlpath);
            FileOutputStream fos = new FileOutputStream(xmlpath);
            properties.store(fos, "write properties");*/

            URL confURL = getClass().getResource("/application.properties");
            String xmlpath = confURL.getFile();
            xmlpath = URLDecoder.decode(xmlpath);
            FileInputStream fis = new FileInputStream(xmlpath);

            // 须用load及save加utf-8参数的形式，否则会乱码
            PropertiesConfiguration conf = new PropertiesConfiguration();
            conf.load(fis, "utf-8");
            fis.close();

            conf.setProperty("spring.datasource.url", url);
            conf.setProperty("spring.datasource.username", user);
            conf.setProperty("spring.datasource.password", pwd);
            if (Global.db.equalsIgnoreCase(Global.DB_POSTGRESQL)) {
                conf.setProperty("spring.datasource.driver-class-name", "org.postgresql.Driver");
            } else {
                conf.setProperty("spring.datasource.driver-class-name", "com.mysql.cj.jdbc.Driver");
            }
            conf.setProperty("spring.datasource.maxActive", String.valueOf(maximumConnectionCount));

            FileOutputStream fos = new FileOutputStream(xmlpath);
            conf.save(fos, "utf-8");
            fos.close();
        } catch (Exception e) {
            LogUtil.getLog(getClass()).error(e);
            return responseUtil.getResultJson(false, e.getMessage()).toString();
        }
        return responseUtil.getResultJson(true).toString();
    }

    @RequestMapping(value = "/checkPool", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    @ResponseBody
    public String checkPool(String database) throws ValidateException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                throw new ValidateException("请以管理员身份登录以后再操作");
            }
        }
        boolean isValid = false;
        String sqlRedmoonid = "select * from redmoonid";
        Conn conn = new Conn(Global.getDefaultDB());
        if (conn.getCon() != null) {
            try {
                conn.executeQuery(sqlRedmoonid);
                isValid = true;
            } catch (SQLException e) {
                return responseUtil.getResultJson(false, e.getMessage()).toString();
            } finally {
                conn.close();
            }
        }
        if (isValid) {
            // 清除缓存
            RMCache.refresh();
            RMCache rmcache = RMCache.getInstance();
            try {
                rmcache.clear();
            } catch (CacheException e) {
                LogUtil.getLog(getClass()).error(e);
            }

            com.redmoon.oa.Config oaCfg = new com.redmoon.oa.Config();
            String strVersion = StrUtil.getNullStr(oaCfg.get("version"));
            double version = StrUtil.toDouble(strVersion, -1);
            try {
                if (version < 3) {
                    // 添加拉单及冲抵字段
                    String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + database + "'";
                    JdbcTemplate jt = new JdbcTemplate();
                    ResultIterator ri = jt.executeQuery(sql);
                    while (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        String tableName = rr.getString(1).toLowerCase();
                        if (tableName.startsWith("ft_")) {
                            sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `cws_flag` TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `cws_status`, ADD COLUMN `cws_quote_id` INTEGER UNSIGNED AFTER `cws_flag`";
                            try {
                                jt.executeUpdate(sql);
                            } catch (Exception e) {
                                LogUtil.getLog(getClass()).error(e);
                            }

                            sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `cws_progress` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `cws_status`";
                            try {
                                jt.executeUpdate(sql);
                            } catch (Exception e) {
                                LogUtil.getLog(getClass()).error(e);
                            }
                        }
                    }
                } else if (version < 4.0) {
                    // 添加cws_parent_form抵字段
                    String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + database + "'";
                    JdbcTemplate jt = new JdbcTemplate();
                    ResultIterator ri = jt.executeQuery(sql);
                    while (ri.hasNext()) {
                        ResultRecord rr = (ResultRecord) ri.next();
                        String tableName = rr.getString(1).toLowerCase();
                        if (tableName.startsWith("ft_")) {
                            // sql = "ALTER TABLE `" + tableName + "` DROP COLUMN `cws_parent_form`";
                            sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `cws_parent_form` varchar(20) AFTER `cws_status`";
                            try {
                                jt.executeUpdate(sql);
                            } catch (Exception e) {
                                LogUtil.getLog(getClass()).error(e);
                            }
                        }
                    }
                } else if (version < 5.0) {
                    // 添加cws_create_date cws_modify_date cws_finish_date抵字段
                    String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + database + "'";
                    JdbcTemplate jt = new JdbcTemplate();
                    ResultIterator ri = jt.executeQuery(sql);
                    while (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        String tableName = rr.getString(1).toLowerCase();
                        if (tableName.startsWith("ft_") && !tableName.endsWith("_log")) {
                            sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `cws_create_date` datetime AFTER `cws_status`,  ADD COLUMN `cws_modify_date` datetime AFTER `cws_status`,  ADD COLUMN `cws_finish_date` datetime AFTER `cws_status`";
                            try {
                                jt.executeUpdate(sql);
                            } catch (Exception e) {
                                LogUtil.getLog(getClass()).error(e);
                            }
                        }
                    }
                } else if (version < 6.0) {
                    // 添加cws_quote_form
                    String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + database + "'";
                    JdbcTemplate jt = new JdbcTemplate();
                    ResultIterator ri = jt.executeQuery(sql);
                    while (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        String tableName = rr.getString(1).toLowerCase();
                        if (tableName.startsWith("ft_") && !tableName.endsWith("_log")) {
                            sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `cws_quote_form` varchar(20) AFTER `cws_status`";
                            try {
                                jt.executeUpdate(sql);
                            } catch (Exception e) {
                                LogUtil.getLog(getClass()).error(e);
                            }
                        }
                    }
                } else if (version == 6.0) {
                    // 添加cws_visited抵字段
                    String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + database + "'";
                    JdbcTemplate jt = new JdbcTemplate();
                    ResultIterator ri = jt.executeQuery(sql);
                    while (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        String tableName = rr.getString(1).toLowerCase();
                        if ("ft_module_log".equals(tableName) || (tableName.startsWith("ft_") && !tableName.endsWith("_log"))) {
                            sql = "ALTER TABLE `" + tableName + "` ADD COLUMN `cws_visited` TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `cws_status`";
                            try {
                                jt.executeUpdate(sql);
                            } catch (Exception e) {
                                LogUtil.getLog(getClass()).error(e);
                            }
                        }
                    }
                } else {
                    // 因嵌套表格2控件的version改为了2，所以需处理一下
                    String sql = "update form_field set description=defaultValue where description='' and macroType='nest_sheet'";
                    try {
                        JdbcTemplate jt = new JdbcTemplate();
                        jt.executeUpdate(sql);
                    } catch (Exception e) {
                        LogUtil.getLog(getClass()).error(e);
                    }
                }
            } catch (SQLException e) {
                return responseUtil.getResultJson(false, e.getMessage()).toString();
            }
        }
        return responseUtil.getResultJson(true).toString();
    }

    /**
     * setup3.jsp调用，设置组织架构根节点名称
     *
     * @return
     * @throws ValidateException
     */
    @RequestMapping(value = "/setupConfig", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    @ResponseBody
    public String setupConfig() throws ValidateException {
        Privilege pvg = new Privilege();
        if (Global.getInstance().isFormalOpen()) {
            if (!pvg.isUserPrivValid(request, Privilege.ADMIN)) {
                throw new ValidateException("请以管理员身份登录以后再操作");
            }
        }

        try {
            XMLConfig cfg = new XMLConfig("config_sys", false, "utf-8");

            String publicIp = ParamUtil.get(request, "publicIp");

            Enumeration e = request.getParameterNames();
            while (e.hasMoreElements()) {
                String fieldName = (String) e.nextElement();
                if (fieldName.startsWith("Application") || fieldName.startsWith("i18n")) {
                    String value = ParamUtil.get(request, fieldName);
                    cfg.set(fieldName, value);

                    LogUtil.getLog(getClass()).info(fieldName + "=" + value);
                }
            }

            // 将Application.server置为外网地址
            if (!"".equals(publicIp.trim())) {
                if (IpUtil.isDomain(publicIp)) {
                    cfg.set("Application.server", publicIp);
                } else {
                    if (!IpUtil.isInnerIP(publicIp)) {
                        cfg.set("Application.server", publicIp);
                    }
                }
            }

            cfg.writemodify();
            Global.getInstance().init();

            // 置realPath
            String realPath = ParamUtil.get(request, "realPath");
            if (realPath.endsWith("\\")) {
                realPath = realPath.substring(0, realPath.length() - 1 ) + "/";
            }
            else {
                // 在末尾自动加上 /
                if (!realPath.endsWith("/")) {
                    realPath += "/";
                }
            }

            configUtil.setApplicationProp("sys.web.uploadPath", realPath);
            Global.getInstance().setRealPath(realPath);
            sysProperties.setUploadPath(realPath);

            String deptName = ParamUtil.get(request, "deptName");
            if (!"".equals(deptName)) {
                Department department = departmentService.getDepartment(ConstUtil.DEPT_ROOT);
                department.setName(deptName);
                departmentService.updateByCode(department);
            }

            String value = ParamUtil.get(request, "Application.name");
            Config myconfig = Config.getInstance();
            myconfig.put("enterprise", value);

            TwoDimensionCode.generate2DCodeByMobileClient();//生成手机端二维码
        } catch (Exception e) {
            LogUtil.getLog(getClass()).error(e);
        }

        // 过滤表单中的图片路径
        String sql = "select code from form";
        FormDb fd = new FormDb();
        Iterator ir = fd.list(sql).iterator();
        while (ir.hasNext()) {
            fd = (FormDb) ir.next();
            String content = fd.getContent();
            content = FormForm.initImgLink(content);
            fd.setContent(content);
            fd.saveContent();
        }
        return responseUtil.getResultJson(true).toString();
    }

    /**
     * 数据库初始库按钮跳转方法
     * 初始化全部表
     *
     * @return
     * @throws ValidateException
     */
    @RequestMapping(value = "/initAllTable", produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    @ResponseBody
    public String initAllTable() throws ValidateException {
        ObjectCache objectCache = new ObjectCache();
        objectCache.init();
        return "";
    }

}